Solved: Refer to Range Names on Different Worksheets

[brorick]

I can't understand why I am having a problem with the combo box on my UserForm. The initialize code on the form refers to main worksheet and the range on that worksheet. The combo box source refers to the range on another worksheet.

Range Name:
ItemsInfo = =OFFSET(frmProductInfo!$A$1,0,0,COUNTA(frmProductInfo!$A:$A),3)


Private Sub UserForm_Initialize()
Worksheets("frmInvoice").Select

Dim irng As Range
With Worksheets("frmProductInfo")
Set irng = Sheet5.Range("ItemsInfo")
cboItem.RowSource = irng.Resize(irng.Rows.Count - 1).Offset(1).Address
End With
End Sub


I am not sure why the combo box source refers to the column a on the main worksheet and not the range.

[mikerickson]

The named range ItemsInfo is on Sheets("frmProductInfo").

What is the name of Sheet5?

"combo box source refers to ... not the range?"
What is the name of the sheet that is populating the combo box?
What sheet do you want populating the combo box?

[brorick]

Thank you for the reply. Sheet5 is named "Product".

[mikerickson]

Since Range("ItemsInfo") is defined to be on frmProductInfo!, and Sheet5 is "Product" I would expect thatSet irng = Sheet5.Range("ItemsInfo") would be a problem.

Do you want the combo box to be populated from Product or frmProductInfo?

[brorick]

Mikerickson, thank you for your help. I am sorry for my previous post. The correct worksheet name for sheet5 is "frmProductInfo". I want the rowsource for my cboItem to be populated by the defined range "ItemsInfo" on Sheet5("frmProductInfo"). I tried various combinations of referencing the range on the worksheet, but I just can't seem to get it to work.

[mikerickson]

.RowSource isn't supported on my computer. I'd use
cboItem.List = Range("ItemsInfo").Value
If you want all three columns to be shown the .ColumnCount should be set to 3.

[brorick]

Mikerickson! You solved it! The reference to .List and .Value did the trick. Thank you for relieving my headache of a situation. I greatly appreciate your help. :clap: