ListFillRange Confusion

Don Payne

New Member
Joined
Oct 20, 2003
Messages
19
I'm having a beginner VB moment.

How come I can call a named range to fill my ComboBox, but I can't just use the same code that I used to get the name with in the first place?
(XP, Control CB on wksht calling list from another wksht)

Thanks for any corrections.

Don


Private Sub ComboBox1_DropButtonClick()

ComboBox1.ListFillRange = "CAList" 'Why does this work...

'And not this??

'ComboBox1.ListFillRange = Sheets("ListPage").Range("D4", Range ("D65536").End(xlUp)).Address

'or this??

'ComboBox1.ListFillRange = Sheet8.Range("D4", Range ("D65536").End(xlUp)).Address

'or other similar attempts?

End Sub
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
One way:

Dim x As Worksheet, y As Long
Set x = Worksheets("ListPage")
y = x.Cells(Rows.Count, 4).End(xlUp).Row
With x
ComboBox1.list = Range(.Cells(4, 4), .Cells(y, 4)).Value
End With
 
Upvote 0
In both scenarios it is very likely that the active sheet is not the sheet 'ListPage', and you're causing an error in:

Range ("D65536").End(xlUp)

because that range is not fully qualified, therefor, VBA is trying to assign it to the active sheet. Then, joining that cell from the activesheet with cell D4 from 'ListPage' causes an error.
 
Upvote 0
Thanks for the help Tom, that works just fine, but calling out the named range works just as well.

I guess I was just being stubborn and trying to find out why that other code didn't work.

Even:

Sheet11.ComboBox1.ListFillRange = Sheet8.Range("D4", Sheet8.Range("D65536").End(xlUp)).Address

returns a value of

$D4:$D34 which is the correct range.

It's just from the Active Sheet 11 and not from the sheet I wanted (Sheet 8)

So I guess, the answer is "I can't do it this way"

Sorry to take up time here.

Thanks, Don
 
Upvote 0
I noticed that as well Juan and crossed posts with you. I think I tried to remedy the problem that you mentioned, but I still couldn't get it to collect the range from the other sheet.

Thanks for looking. I appreciate it.
 
Upvote 0
Yes, you can do it that way...

Excel still doesn't know the correct range. Look, you're telling the control that the range is

D4:D34

but that's it ! it doesn't know from WHICH sheet !!! try doing it manually like

Sheet1!D4:D34

in the properties of the listbox. It should work ok. So, all you have to do is explicitly state the sheet name in there... because the ListFillRange is just a string, it doesn't know any "better"...
 
Upvote 0
Mr. Payne - -

Sorry, I did not go into detail about why your code was not working. In addition to what Juan said about the sheet references, the other 2 problems you had were, you were trying to collect an Address instead of a Value, and the syntax of your range reference code was not correct (notice the placement of parentheses in my code vs yours).

Anyway, all's well that end well...hope this is solved. Thanks also, JPG.
 
Upvote 0
Wow - - this thread is destined for simultaneous postings !!

Hey Juan, hope things are settling in for you OK there in Illinois. I'll be passing through there next week but only for a 2-hour layover on my way to NY. Happy holidays !!
 
Upvote 0
Just to provide one last wallop on the dead horse.

Tom,

I tried to use .Value (in both your code and mine) for the ListFillRange, but it keeps giving me a MisMatch error.

Juan,

I understand what you are saying about telling the control where to get the range from, but I'm just not getting it yet. All part of the learning process, I guess. I'll revisit this problem when I know more and can converse with you guys on a more equal level.


Sheet11.ComboBox1.ListFillRange = Sheet8.Range(Sheet8.Range("D4"), Sheet8.Range("D65536").End(xlUp)).Address(Value=mismatch)

So, I'm calling it a weekend. Many thanks to you both for all you do. It is always much appreciated.

Don
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,696
Members
448,293
Latest member
jin kazuya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top