Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
eliminate blanks from listbox rowsource
I am successfully updating the contents of a listbox using the
selection of a combobox. the listbox is populated by means of setting the rowsource to a named range when the combobox is changed. unfortunately, the named ranges will sometimes have blanks which I do not want displayed in the listbox. my current code looks like this: Private Sub ComboBoxgroupHSS_DropButtonClick() With UserFormDesign .ListBoxHSS.RowSource = .ComboBoxgroupHSS.Value End With End Sub Is there a way to eliminate these blanks without altering my code too much? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
eliminate blanks from listbox rowsource
Rather than assign the named range to the rowsource property, run a loop
against the named range and test for blank / empty cells, then add non-empty cells into the list control. Also use the comboxbox change event. Not shown but you should check that the range chosen is valid? Private Sub ComboBoxgroupHSS_Change() Dim c As Range With UserFormDesign .ListBoxHSS.Clear For Each c In Range(.ComboBoxgroupHSS.Value) If Len(Trim(c.Value)) 0 Then .ListBoxHSS.AddItem c.Value Next End With End Sub -- Cheers Nigel "Jacob" wrote in message ps.com... I am successfully updating the contents of a listbox using the selection of a combobox. the listbox is populated by means of setting the rowsource to a named range when the combobox is changed. unfortunately, the named ranges will sometimes have blanks which I do not want displayed in the listbox. my current code looks like this: Private Sub ComboBoxgroupHSS_DropButtonClick() With UserFormDesign .ListBoxHSS.RowSource = .ComboBoxgroupHSS.Value End With End Sub Is there a way to eliminate these blanks without altering my code too much? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
eliminate blanks from listbox rowsource
thanks
This makes sense, but I keep getting an error where I have .ListBoxHSS and I'm not sure why? On Nov 1, 11:43 am, "Nigel" wrote: Rather than assign the named range to the rowsource property, run a loop against the named range and test for blank / empty cells, then add non-empty cells into the list control. Also use the comboxbox change event. Not shown but you should check that the range chosen is valid? Private Sub ComboBoxgroupHSS_Change() Dim c As Range With UserFormDesign .ListBoxHSS.Clear For Each c In Range(.ComboBoxgroupHSS.Value) If Len(Trim(c.Value)) 0 Then .ListBoxHSS.AddItem c.Value Next End With End Sub -- Cheers Nigel "Jacob" wrote in glegroups.com... I am successfully updating the contents of a listbox using the selection of a combobox. the listbox is populated by means of setting the rowsource to a named range when the combobox is changed. unfortunately, the named ranges will sometimes have blanks which I do not want displayed in the listbox. my current code looks like this: Private Sub ComboBoxgroupHSS_DropButtonClick() With UserFormDesign .ListBoxHSS.RowSource = .ComboBoxgroupHSS.Value End With End Sub Is there a way to eliminate these blanks without altering my code too much? Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
eliminate blanks from listbox rowsource
I figured out my problem but ran into another. I had to remove the
rowsource that was specified for the listbox. but now I have a problem because my range was two columns. it is now treating it as one column. On Nov 1, 12:24 pm, "Jacob" wrote: thanks This makes sense, but I keep getting an error where I have .ListBoxHSS and I'm not sure why? On Nov 1, 11:43 am, "Nigel" wrote: Rather than assign the named range to the rowsource property, run a loop against the named range and test for blank / empty cells, then add non-empty cells into the list control. Also use the comboxbox change event. Not shown but you should check that the range chosen is valid? Private Sub ComboBoxgroupHSS_Change() Dim c As Range With UserFormDesign .ListBoxHSS.Clear For Each c In Range(.ComboBoxgroupHSS.Value) If Len(Trim(c.Value)) 0 Then .ListBoxHSS.AddItem c.Value Next End With End Sub -- Cheers Nigel "Jacob" wrote in glegroups.com... I am successfully updating the contents of a listbox using the selection of a combobox. the listbox is populated by means of setting the rowsource to a named range when the combobox is changed. unfortunately, the named ranges will sometimes have blanks which I do not want displayed in the listbox. my current code looks like this: Private Sub ComboBoxgroupHSS_DropButtonClick() With UserFormDesign .ListBoxHSS.RowSource = .ComboBoxgroupHSS.Value End With End Sub Is there a way to eliminate these blanks without altering my code too much? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Listbox - Rowsource | Excel Programming | |||
RowSource in ListBox | Excel Programming | |||
RowSource in ListBox | Excel Programming | |||
Is refreshing listbox rowsource in listbox click event possible? | Excel Programming | |||
listbox rowsource | Excel Programming |