none
ActiveX ComboBox change ListFillRange RRS feed

  • Question

  • Hi,

    I am struggling to modify the ListFillRange with VBA.

    The goal is to use another list depending on a known condition. 

    The code below with combo and list on the same sheet works fine.

    Private Sub ComboBox1_GotFocus()
       ComboBox1.ListFillRange = Range("List").Address
    End Sub
    

    What I need is to assign a list on another sheet.

    Private Sub ComboBox1_GotFocus()
       ComboBox1.ListFillRange = Range("List2").Address
    End Sub
    But that gives a runtime error.

    What am I doing wrong, is it even possible or do you know a work around.

    My sample file here:

    https://1drv.ms/x/s!AmIR8Z3XdrKRkS77_g5ylvov7daL

    Regards,

    JP

    Wednesday, February 6, 2019 5:31 PM

Answers

  • You can use

        ComboBox1.ListFillRange = ActiveWorkbook.Names("List2").RefersTo


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by JP Ronse Thursday, February 7, 2019 4:00 PM
    Wednesday, February 6, 2019 8:21 PM

All replies

  • You can use

        ComboBox1.ListFillRange = ActiveWorkbook.Names("List2").RefersTo


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by JP Ronse Thursday, February 7, 2019 4:00 PM
    Wednesday, February 6, 2019 8:21 PM
  • Thanks Hans,

    Works perfect.

    Thursday, February 7, 2019 4:00 PM