How to populate a listbox based on a conditional statement

Question

I'm trying to populate a listbox in a userform, but the listbox needs to change based on what is in a cell on the active sheet. The complication is that I am trying to refer to a named range, which is on another sheet. So for example, if the cell says "hi" - I would want to check that the cell says hi, and then go to the named range on another sheet called "hi" and bring in the values in that range into the listbox.

Here's what I have so far:

Private Sub UserForm_Initialize()

'Populate Combobox Based on Cell Value
Dim celltxt As String
celltxt = ActiveSheet.Range("cellTest").Text

    If InStr(1, celltxt, "hi") Then
        'Code to bring in routes from named range called "hi"
        ListBox1.RowSource = Worksheets("Sheet4").Range("hi").Value
    End If

End Sub

I would repeat the "if-end if" segment of code multiple times based on other cell values, such as "hey" or "what's up."

I keep getting a run-time 1004 error. Help!!

Answer 1

The problem was that I had renamed "Sheet 4" as "DropDown" because it was where I was storing all of my dropdown menus. The code should read:

Private Sub UserForm_Initialize()

'Populate Combobox Based on Cell Value
Dim celltxt As String
celltxt = ActiveSheet.Range("cellTest").Text

    If InStr(1, celltxt, "hi") Then
        'Code to bring in routes from named range called "hi"
        ListBox1.RowSource = Worksheets("DropDown").Range("hi").Value
    End If

End Sub

And that works great!