How to update a ComboBox's drop down list

[feipezi]

Hi,
I have a ComboBox on Sheet1 with a named range "testrange" as ListFillRange. But when I update the named range, like reducing or increasing the rows of the range and then re-define it, the change will not reflect on the drop-down list of the ComboBox, unless I delete the named range or the ActiveX control, and re-create them.
Application.Volatile will help or not?
Please advise.
Thanks in advance.

 

[SkipVought]

Hi,

Try this...

Private Sub ComboBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    ComboBox1.ListFillRange = [testrange].Address
End Sub

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[feipezi]

Hi Skip,
You're the man. It really worked.
Thanks a lot and have a great holiday.

 

[feipezi]

Skip,
I have another question.
If I have 7 ComboBoxes on a single sheet with quite a few more buttons. Sometimes I have problem identifying them. When I used the following:

Sub test()
For Each s In ActiveSheet.Shapes
MsgBox s.Name
Next
End Sub

it can give me the names for sure but what if I just want one of them. I know I can go to the Property Windows and get it in Design mode. Is there any way we can select a particular shape by VBA? Like the code you presented:

ComboBox1.ListFillRange...

If I have more than one shape, I thought it was ComboBox1 but actually it was ComboBox3 that I wanted.
Thanks again.

 

[SkipVought]

"...I have 7 ComboBoxes on a single sheet ..."

I'd suggest that you consider using only ONE combobox. You can make it VISIBLE in context of a single cell selection. For instance, column A2:A10 might display a combo for a NAME, column B2:B10 display a combo for a TEAM. So you'd use the Worksheet_SelectionChange event to determine which PROPERTIES to attach to the combo, like the listfillrange and OnAction procedure, position & size the combo and make it visible.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[feipezi]

Thanks, Skip! I'll give it a try on the weekend.
Take care.