set variable range to fill combo box on another sheet

[tobin1]

Howdy,

I'm having difficulty doing something that seems like it should be very simple.

Private Sub cbx_RevLookUp_DropButtonClick()

Dim ListRng As String
Application.ScreenUpdating = False
ListRng = Sheet2.Range("Y102:Y108")

Sheet1.cbx_RevLookUp.ListFillRange = ListRng

End Sub

I have a combo box on the first sheet of an Excel file. I need to fill the combo box with data in a range on the second sheet. The range needs to accommodate a variable for the row because it could grow as the user continues with other parts of the macro.

I've looked and searched for examples but can't seem to find one that I can make work. I've been trying to use .listFillRange method because it is supposed to show the list instead of adding to the bottom of the list like .AddItem seems to do.

Can someone show me how to do this?



Tobin Sparks

http://www.nov.com

 

[PHV]

I'd try this:
ListRng = "Sheet2!Y102:Y108"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tobin1]

Thanks PHV for your response. Interesting try but still can't make it work.

Apparently I need to get the range values as string data type not just an empty "string".

Any other suggestions?



Tobin Sparks

http://www.nov.com

 

[SkipVought]

Why do you have the code to assign the ListFillRange in the Click event to the same control?

BTW, this code works but initialize the control first, then do something else in the click event.

    ListRng = "Sheet2!Y102:Y108" 
    
    Sheet1.cbx_RevLookUp.ListFillRange = ListRng

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

With a combo created with the controls tool box:

Sheet1.OLEObjects("cbx_RevLookUp").ListFillRange = "Sheet2!Y102:Y108"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tobin1]

Howdy,

Thanks again PHV for your interesting suggestion.
Maybe we have different versions of Excel.
I'm still using 2003.

I tried all of the following:

Didn't work even in Sub Worksheet_Activate()

Sheet1.OLEObjects("cbx_RevLookUp").ListFillRange = "Sheet2!Y102:Y108"

Didn't work even in Sub Worksheet_Activate()

Sheet1.OLEObjects("cbx_RevLookUp").ListFillRange = "Sheet1!Y102:Y108"
' I Copied the range to the first sheet just for testing

Does work in all cases

Sheet1.OLEObjects("cbx_RevLookUp").ListFillRange = "Y102:Y108"

For some reason I'm not connection to the second sheet to get those range values.

Any other suggestions?

Tobin Sparks

http://www.nov.com

 

[tobin1]

Howdy PHV,

OOPS my fault .
The tab name for the second sheet is not Sheet2. When I use the tab name it works perfect even in a click event.

Thanks again - you were very helpful

Tobin Sparks

http://www.nov.com