Populate listbox

[MavicPro]

Hi All
I am currently working on a project which uses dependant drop down boxes. Which work all very well, however the issue I am having is that one of the boxes, which is a listbox; needs to be populated with dynamic data from columns C to K , dependant on what's in Combobox1. I have included the code but it is obviously missing the code for getting the data into listbox1.

What I'm really looking for is the correct syntax.

e.g combobox1 shows 'WEST'. Textbox 11,12,13 & 14 populate with data no problem, however listbox1 needs populating from a different sheet called 'SITES' and the data is a list of named ranges, (NORTH, EAST, SOUTH, WEST, etc) so I basically need to know how to get the named range into a 1 column listbox1!


Any help appreciated.

Private Sub ComboBox1_Change()
 
Dim Look As Range
        Set Look = Worksheets("OFFICES").Range("A2:K10").Find(what:=Me.ComboBox1.Value, _
                                                       LookIn:=xlValues, _
                                                       lookat:=xlWhole, _
                                                       SearchOrder:=xlByRows, _
                                                       SearchDirection:=xlNext, _
                                                       MatchCase:=False)
 
    If Look Is Nothing Then
            MsgBox "No match for " & Me.ComboBox1.Value, , "No Match Found"
 
        Else
 
            Me.TextBox11.Value = Worksheets("OFFICES").Range("B" & Look.Row)
            Me.TextBox12.Value = Worksheets("OFFICES ").Range("C" & Look.Row)
            Me.TextBox13.Value = Worksheets("OFFICES ").Range("F" & Look.Row)
            Me.TextBox14.Value = Worksheets("OFFICES ").Range("H" & Look.Row)
 
 
 
        End If
 
 
End Sub

 

[Akuini]

so I basically need to know how to get the named range into a 1 column listbox1!

Do you mean you want to populate the listbox from a named range selected in the combobox?
E.g if you select WEST in the combobox then the listbox will be filled with the values of WEST named range.
Maybe this:

ListBox1.List = Range(Combobox1.Value).Value

 

[My Aswer Is This]

If you want to load the values from four named ranges on a sheet named "SITES"
Into Activex ListBox1

Try this:

Private Sub CommandButton1_Click()
'Modified  12/18/2018  4:11:28 PM  EST
Dim r As Range
Dim Del As Variant
Del = Array("North", "South", "East", "West")
ListBox1.Clear
For i = 0 To 3
    For Each r In Sheets("SITES").Range(Del(i))
        ListBox1.AddItem r.Value
    Next
Next
End Sub

 

[MavicPro]

Hi Again
Thanks for reply..
Akuini - Works like a dream!
My Answer is this - Thanks also for your reply, However I have more than 4 Named ranges, In fact 14 (and probably more as time goes on!) but I have been playing with your answer for a different part of the userform that I was having issues with!

So basically you both solved my issue.

Thank you very much, I know where to look for more help in future

HAPPY XMAS ALL

 

[My Aswer Is This]

Glad we were able to help you.
Come back here to Mr. Excel next time you need additional assistance.
Wishing you a nice Christmas also.

Hi Again
Thanks for reply..
Akuini - Works like a dream!
My Answer is this - Thanks also for your reply, However I have more than 4 Named ranges, In fact 14 (and probably more as time goes on!) but I have been playing with your answer for a different part of the userform that I was having issues with!

So basically you both solved my issue.

Thank you very much, I know where to look for more help in future

HAPPY XMAS ALL