How to set rowsource for a combo using a range variable

[David Jones]

Hi all. I'm struggling with some code to allow me to populate a combo box with values in a dynamic range that has been set as a global variable.

In a module called GlobalVariablesAndFunctions I have the following code:

Global rngBaseUnits As Range
Set rngBaseUnits = Worksheets("Pricing").Range(tmpPrefix & "_Base_Units")

In the above example tmpPrefix is set elsewhere to a 3 letter prefix of COT

The combo box is called Install_BaseUnit and sits on a page of a multipage control called Installation which is part of a userform called SUAgreement.

In the Userform Initialize section of SUAgreement I've tried a variety of ways to set the RowSource property of Install_BaseUnit to be the range defined as rngBaseUnits but nothing I've tried will work.

I picked this example up form another post on this forum but could not find a way of adapting it to use my rngBaseUnits global

PrivateSub UserForm_Initialize()
    Range("B2", Range("B" & Rows.Count).End(xlUp)).Name = "Dynamic"
    Me.ComboBox1.RowSource = "Dynamic"
EndSub

Any help greatly appreciated

 

[Debaser]

Try using:

Me.Install_BaseUnit.Rowsource = rngBaseUnits.Name.Name