Using a Dynamic Range as a "ComboBox RowSource" in VBA UserForm

[ilkay]

Hey everyone.
I have an excel workbook that contains two sheets (databases) and a UserForm. On the UserForm, I have a ComboBox. What I want to do is to set a dynamic range in "Sheet2" as a RowSource for the ComboBox. Actually I am able to set it, but if I am on "Sheet1" ComboBox takes its RowSource values from "Sheet1" although I defined "Sheet2" in my code. Here is the code:

Dim r As Range
With Worksheets("Sheet2")
        Set r = .Range("B2", .Range("B65536").End(xlUp))
End With

ComboBox1.RowSource = r.Address

Any help would be appreciated.
Thanks in advance.

 

[Jon von der Heyden]

Welcome to the board.

That's because the address property you have used doesn't qualify the sheet name. Try;

ComboBox1.RowSource = "Sheet2!" & r.Address

 

[ilkay]

I appreciate your flash help "Jon von der Heyden"!
You are a life saver. It works now. Thank you!