Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

set variable range to fill combo box on another sheet

Status
Not open for further replies.

tobin1

Technical User
Mar 10, 2008
7
US
Howdy,

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

Code:
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
 
I'd try this:
ListRng = "Sheet2!Y102:Y108"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
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
 



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.
Code:
    ListRng = "Sheet2!Y102:Y108" 
    
    Sheet1.cbx_RevLookUp.ListFillRange = ListRng

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
With a combo created with the controls tool box:
Code:
Sheet1.OLEObjects("cbx_RevLookUp").ListFillRange = "Sheet2!Y102:Y108"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
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()
Code:
Sheet1.OLEObjects("cbx_RevLookUp").ListFillRange = "Sheet2!Y102:Y108"
Didn't work even in Sub Worksheet_Activate()
Code:
Sheet1.OLEObjects("cbx_RevLookUp").ListFillRange = "Sheet1!Y102:Y108"
' I Copied the range to the first sheet just for testing
Does work in all cases
Code:
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
 
Howdy PHV,

OOPS my fault [dazed] .
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top