Ensure that assigning RowSource will not fail

Question

I have 2 ComboBoxes which I populate using RowSource.
Combobox2 RowSource change based on the value of Combobox1.

I have this code:

If Combobox1.Value = "Item1" Then Combobox2.RowSource = "Sheet1!Item1"

Sheet1 is the name of the sheet, Item1 is a named range.

Problem:
This works fine if the only workbook open is the one containing this code.
However, if another workbook is open, it fails to reference the RowSource correctly.
How can I improve this?

Answer 1

To improve this, include the Filename of the workbook that contains the RowSource like this:

If ComboBox1.Value = "Item1" Then ComboBox2.RowSource = "'[Test.xlsm]Sheet1'!Item1"

Or better yet improve it by declaring variables:

Dim wb As Workbook, ws As Worksheet

Set wb = Thisworkbook
Set ws = wb.Sheets("Sheet1")

If ComboBox1.Value = "Item1" Then _
   ComboBox2.RowSource = "'[" & wb.Name & "]" & ws.name & "'!" & "Item1"

Answer 2

Old post I know but I came across it when trying to figure out the RowSource function (somewhat peculiar calling it RowSource when it takes data from a column).

Anywho, I've been playing around with it a bit & believe the below simplifies it, saves you having to remember when all the quotes, exclamation marks & brackets go.

If Combobox1.Value = "Item1" Then Combobox2.RowSource = ThisWorkbook.Names("Item1").RefersTo

Or if you're not using a named range:

If Combobox1.Value = "Item1" Then Combobox2.RowSource = ThisWorkbook.Worksheets("Sheet1").Range("A1:A5").Address

Answer 3

You can use the combobox's List property instead, something like:

If Combobox1.Value = "Item1" Then 
    Me.ComboBox2.List = Application.WorksheetFunction.Transpose(Workbooks("test").Worksheets("Sheet1").Range("Item1"))
End If

Application.Transpose is needed to turn the range into a one-dimensional array.