listbox change with combobox selection

[Jacob]

I am trying to get a listbox to update based on a combobox selection.
Unfortunately my combobox options are W4,W5,W6 etc. which I cannot use
to name ranges in excel. Otherwise I would have used the following
code:

Private Sub ComboBoxgroupW_Change()

UserFormDesign.ListBoxW.RowSource = UserFormDesign.ComboBoxgroupW.Value

End Sub

that way each time the combobox was changed, the list would be updated
with the range that corresponded to the selection. If there is a way
around that, without renaming the combobox choices, that would be
great. here is my second option which is not working at all:

Private Sub ComboBoxgroupW_Change()

I = UserFormDesign.ComboBoxgroupW.Value

Select Case I
Case W4
UserFormDesign.ListBoxW.RowSource = WIDE4
Case W5
UserFormDesign.ListBoxW.RowSource = WIDE5
Case W6
UserFormDesign.ListBoxW.RowSource = WIDE6
Case W8
UserFormDesign.ListBoxW.RowSource = WIDE8
Case W10
UserFormDesign.ListBoxW.RowSource = WIDE10
Case W12
UserFormDesign.ListBoxW.RowSource = WIDE12
Case W14
UserFormDesign.ListBoxW.RowSource = WIDE14
Case W16
UserFormDesign.ListBoxW.RowSource = WIDE16
Case W18
UserFormDesign.ListBoxW.RowSource = WIDE18
Case W21
UserFormDesign.ListBoxW.RowSource = WIDE21
Case W24
UserFormDesign.ListBoxW.RowSource = WIDE24
Case W27
UserFormDesign.ListBoxW.RowSource = WIDE27
Case W30
UserFormDesign.ListBoxW.RowSource = WIDE30
Case W33
UserFormDesign.ListBoxW.RowSource = WIDE33
Case W36
UserFormDesign.ListBoxW.RowSource = WIDE36
Case W40
UserFormDesign.ListBoxW.RowSource = WIDE40
Case W44
UserFormDesign.ListBoxW.RowSource = WIDE44
Case ALL
UserFormDesign.ListBoxW.RowSource = ALLW
End Select
End Sub


WIDE4, WIDE5, WIDE6, etc. are the named ranges that I want to show in
the listbox. Nothing happens when I run this code. what am I doing
wrong?

 

[Bob Phillips]

How about

Private Sub ComboBoxgroupW_Change()

With UserFormDesign
.ListBoxW.RowSource = range(.ComboBoxgroupW.Value).address
End With

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Jacob]

that does the same thing as my first code which does not work. that is
assigning W4,W5,W6 etc. to the RowSource of the listbox which is not
correct since excel would not let me name the ranges W4, W5, W6 etc. I
need to get the RowSource of the list box to WIDE4, WIDE5, etc.
without changing the combobox list. thanks for your help though.