How to Load Combo Box with Named Range

[EricG]

I have a named range "SNList" on worksheet "SN List". The named range
consists of two columns and 30 rows. I want to fill a combobox (named
"CMB_SN_List") using the contents of the named range. The combobox is set up
with two columns. The combobox is on worksheet "Weekly Data".

What is the proper syntax for that?

I have tried things like:

Worksheets("Weekly Data").OLEObjects("CMB_SN_List").ListFillRange =
Worksheets("SN List").Range("SNList")

but that generates an error. Do I have to use the .AddItem method since I
have more than one column?

Thanks,

Eric

 

[EricG]

Never mind. It's as simple as

..ListFillRange = "SNList"

 

[Patrick Molloy]

You're using the ActiveX combobox, so its


Worksheets("SN List").CMB_SN_List.ListFillRange = "SNList"

 

[EricG]

Thanks for the reply, Patrick.

One more question - after the user selects whatever from the combbox, I am
using ".Visible = false" to hide it. However, its image stays on the screen.
If I scroll it out of sight and back in, it's gone, which tells me it is
invisible, so the remaining image must be a "ghost".

Is there a neat trick to force a screen redraw to get rid of this "ghost"
image of the combobox?

Thanks,

Eric

 

[Patrick Molloy]

interesting. maybe do the scroll in vba after setting the visible state?
perhaps turning off screen updating to avoid flicker