Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
listbox change with combobox selection
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
listbox change with combobox selection
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" wrote in message ups.com... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
listbox change with combobox selection
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. Bob Phillips wrote: 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" wrote in message ups.com... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Chart content to change according to a selection on a combobox | Charts and Charting in Excel | |||
fill combobox depending on selection from another combobox | Excel Discussion (Misc queries) | |||
Combobox items determined by the selection in another combobox | Excel Programming | |||
Listbox Event for selection/scroll change | Excel Programming | |||
Combobox v Listbox | New Users to Excel |