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

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

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

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