|
update listbox from combobox selection
I am trying to update the contents of a listbox based on combobox
selection. I want to listbox to update with specific named ranges that correspond to the combobox selections. unfortunately the named ranges are not identical to the combobox choices because I was unable to name ranges W4, W5, W6, etc. so I could not simply directly set userform1.listbox1.rowsource = userform1.combobox1.value I tried using the following code instead and it does not work at all (notice the variations on the right side of the equal sign. I tried everything I could think of): Sub ComboBoxgroupW_Change() r = UserFormDesign.ComboBoxgroupW.Text Select Case r Case W4 UserFormDesign.ListBoxW.RowSource = Range("WID4") Case W5 UserFormDesign.ListBoxW.RowSource = Range("WID5") Case W6 UserFormDesign.ListBoxW.RowSource = Range("WID6") Case W8 UserFormDesign.ListBoxW.RowSource = Range("WID8") Case W10 UserFormDesign.ListBoxW.RowSource = Range("WID10") Case W12 UserFormDesign.ListBoxW.RowSource = Range(WID12).Address Case W14 UserFormDesign.ListBoxW.RowSource = Range("WID14").Address Case W16 UserFormDesign.ListBoxW.RowSource = Range(WID16) Case W18 UserFormDesign.ListBoxW.RowSource = WID18 Case W21 UserFormDesign.ListBoxW.RowSource = "WID21" 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 = ALLWF End Select End Sub I notice in the properties for a combobox, that each entry has a value and a text. Is there a way to set the value to one thing (my undesirably named ranges) and the actual text that the user sees to something else? this might solve my problem. Thanks! |
update listbox from combobox selection
Yes, you can have a bound column that produces the value of the combobox. So
if you had a two column list, you could set the columnwidth of the second column to zero and bind to that column so that is the value returned. I would think object.rowsource = "WID8 should work. Private Sub UserForm_Initialize() Me.ListBox1.RowSource = "WIDE8" End Sub worked fine for me as a simple test/demonstration. As did this: Private Sub UserForm_Initialize() Me.ComboBox1.Value = "WIDE8" Me.ListBox1.RowSource = Me.ComboBox1.Value End Sub -- Regards, Tom Ogilvy "Jacob" wrote: I am trying to update the contents of a listbox based on combobox selection. I want to listbox to update with specific named ranges that correspond to the combobox selections. unfortunately the named ranges are not identical to the combobox choices because I was unable to name ranges W4, W5, W6, etc. so I could not simply directly set userform1.listbox1.rowsource = userform1.combobox1.value I tried using the following code instead and it does not work at all (notice the variations on the right side of the equal sign. I tried everything I could think of): Sub ComboBoxgroupW_Change() r = UserFormDesign.ComboBoxgroupW.Text Select Case r Case W4 UserFormDesign.ListBoxW.RowSource = Range("WID4") Case W5 UserFormDesign.ListBoxW.RowSource = Range("WID5") Case W6 UserFormDesign.ListBoxW.RowSource = Range("WID6") Case W8 UserFormDesign.ListBoxW.RowSource = Range("WID8") Case W10 UserFormDesign.ListBoxW.RowSource = Range("WID10") Case W12 UserFormDesign.ListBoxW.RowSource = Range(WID12).Address Case W14 UserFormDesign.ListBoxW.RowSource = Range("WID14").Address Case W16 UserFormDesign.ListBoxW.RowSource = Range(WID16) Case W18 UserFormDesign.ListBoxW.RowSource = WID18 Case W21 UserFormDesign.ListBoxW.RowSource = "WID21" 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 = ALLWF End Select End Sub I notice in the properties for a combobox, that each entry has a value and a text. Is there a way to set the value to one thing (my undesirably named ranges) and the actual text that the user sees to something else? this might solve my problem. Thanks! |
update listbox from combobox selection
thanks for the help. I figured out how to bind the values I need, and
that part seems to be working fine. however, I cannot get it to update properly. here is the code I am using. It is giving me an error. Private Sub ComboBoxgroupW_Change() UserFormDesign.ListBoxW.RowSource = UserFormDesign.ComboBoxgroupW.Value End Sub I want the list to update whenever the combobox is updated. Tom Ogilvy wrote: Yes, you can have a bound column that produces the value of the combobox. So if you had a two column list, you could set the columnwidth of the second column to zero and bind to that column so that is the value returned. I would think object.rowsource = "WID8 should work. Private Sub UserForm_Initialize() Me.ListBox1.RowSource = "WIDE8" End Sub worked fine for me as a simple test/demonstration. As did this: Private Sub UserForm_Initialize() Me.ComboBox1.Value = "WIDE8" Me.ListBox1.RowSource = Me.ComboBox1.Value End Sub -- Regards, Tom Ogilvy "Jacob" wrote: I am trying to update the contents of a listbox based on combobox selection. I want to listbox to update with specific named ranges that correspond to the combobox selections. unfortunately the named ranges are not identical to the combobox choices because I was unable to name ranges W4, W5, W6, etc. so I could not simply directly set userform1.listbox1.rowsource = userform1.combobox1.value I tried using the following code instead and it does not work at all (notice the variations on the right side of the equal sign. I tried everything I could think of): Sub ComboBoxgroupW_Change() r = UserFormDesign.ComboBoxgroupW.Text Select Case r Case W4 UserFormDesign.ListBoxW.RowSource = Range("WID4") Case W5 UserFormDesign.ListBoxW.RowSource = Range("WID5") Case W6 UserFormDesign.ListBoxW.RowSource = Range("WID6") Case W8 UserFormDesign.ListBoxW.RowSource = Range("WID8") Case W10 UserFormDesign.ListBoxW.RowSource = Range("WID10") Case W12 UserFormDesign.ListBoxW.RowSource = Range(WID12).Address Case W14 UserFormDesign.ListBoxW.RowSource = Range("WID14").Address Case W16 UserFormDesign.ListBoxW.RowSource = Range(WID16) Case W18 UserFormDesign.ListBoxW.RowSource = WID18 Case W21 UserFormDesign.ListBoxW.RowSource = "WID21" 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 = ALLWF End Select End Sub I notice in the properties for a combobox, that each entry has a value and a text. Is there a way to set the value to one thing (my undesirably named ranges) and the actual text that the user sees to something else? this might solve my problem. Thanks! |
update listbox from combobox selection
Nevermind, I figured it out. thanks for your help. I ended up using
this: Private Sub ComboBoxgroupW_Change() With UserFormDesign .ListBoxW.RowSource = .ComboBoxgroupW.Value End With End Sub Tom Ogilvy wrote: Yes, you can have a bound column that produces the value of the combobox. So if you had a two column list, you could set the columnwidth of the second column to zero and bind to that column so that is the value returned. I would think object.rowsource = "WID8 should work. Private Sub UserForm_Initialize() Me.ListBox1.RowSource = "WIDE8" End Sub worked fine for me as a simple test/demonstration. As did this: Private Sub UserForm_Initialize() Me.ComboBox1.Value = "WIDE8" Me.ListBox1.RowSource = Me.ComboBox1.Value End Sub -- Regards, Tom Ogilvy "Jacob" wrote: I am trying to update the contents of a listbox based on combobox selection. I want to listbox to update with specific named ranges that correspond to the combobox selections. unfortunately the named ranges are not identical to the combobox choices because I was unable to name ranges W4, W5, W6, etc. so I could not simply directly set userform1.listbox1.rowsource = userform1.combobox1.value I tried using the following code instead and it does not work at all (notice the variations on the right side of the equal sign. I tried everything I could think of): Sub ComboBoxgroupW_Change() r = UserFormDesign.ComboBoxgroupW.Text Select Case r Case W4 UserFormDesign.ListBoxW.RowSource = Range("WID4") Case W5 UserFormDesign.ListBoxW.RowSource = Range("WID5") Case W6 UserFormDesign.ListBoxW.RowSource = Range("WID6") Case W8 UserFormDesign.ListBoxW.RowSource = Range("WID8") Case W10 UserFormDesign.ListBoxW.RowSource = Range("WID10") Case W12 UserFormDesign.ListBoxW.RowSource = Range(WID12).Address Case W14 UserFormDesign.ListBoxW.RowSource = Range("WID14").Address Case W16 UserFormDesign.ListBoxW.RowSource = Range(WID16) Case W18 UserFormDesign.ListBoxW.RowSource = WID18 Case W21 UserFormDesign.ListBoxW.RowSource = "WID21" 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 = ALLWF End Select End Sub I notice in the properties for a combobox, that each entry has a value and a text. Is there a way to set the value to one thing (my undesirably named ranges) and the actual text that the user sees to something else? this might solve my problem. Thanks! |
| All times are GMT +1. The time now is 11:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com