Values of combobox with internal sheet name

Question

I have an Excel spreadsheet in which I want to apply a combobox. The values for the drop down of the combobox are shown in Column A in the Excel sheet:

            A
1        Test 1
2        Test 2
3        Test 3
4        Test 4

So far I use the following code for the combobox:

Private Sub UserForm_Initialize()
ComboBox1.RowSource = "Sheet1!A1:A4"
End Sub

This code works perfectly. However, when I change the name of the sheet from "Sheet1" to for example "Marketing" the code won't run anymore. Therefore, preferable the code should refer to the internal sheet name in Excel. Thus I created the following code:

Private Sub UserForm_Initialize()
ComboBox1.RowSource = Sheet1.Range("A1:A4")
End Sub

However, with this code I get runtime error 424. Do you have any idea how I can use the internal sheet name to get the values from the sheet into the drop down menu of the combobox?

Slai Slai · Accepted Answer · 2017-03-17T09:39:18

ComboBox1.RowSource = Sheet1.Name & "!A1:A4"

You can also add the range as a hidden Name, so that it auto updates (not tested):

ThisWorkbook.Names.Add Name:="myName", RefersTo:="=Sheet1!A1:A4", Visible:=False

and then in the UserForm:

ComboBox1.RowSource = "myName"

Values of combobox with internal sheet name

1 Answers