populate 2 column listbox from one named range

[Jacob]

I need to populate a listbox with two columns from a named range (which
changes based on a combobox selection). the named range will consist
of two columns as well. here is what I have right now thanks to Nigel:

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

The column count of the listbox is set to 2. this routine applies each
non zero cell from the range (regardless of column location) and puts
it into the first column of the listbox. I would like the first
column of the range to go into the first column of the listbox and
similarly the second column of the range into the second column of the
listbox. how is this done?

thanks

 

[Guest]

Private Sub ComboBoxgroupHSS_Change()

Dim c As Range
With UserFormDesign
.ListBoxHSS.Clear
For Each c In Range(.ComboBoxgroupHSS.Value).columns(1).Cells
If Len(Trim(c.Value)) > 0 Then
.ListBoxHSS.AddItem c.Value
.ListBoxHSS.List(.ListBoxHSS.Listcount - 1,1) = C.offset(0,1).Value
end if
Next
End With

 

[Jacob]

Thanks, that seems to work, but I'm getting blank spaces at the end
sometimes that when clicked cause an error. is there a way to
eliminate the error. I don't want anything to happen when I click the
blank space.

 

[Tom Ogilvy]

Your code guards against that with the line
If Len(Trim(c.Value)) > 0 Then

You will need to inspect your data and see what is in the cells that causes
and apparently blank cell to be included. It must contain a character like
chr(160) - non breaking space - which isn't eliminated by the trim function.
Clean up your data and your problems should clear up.

--
Regards,
Tom Ogilvy

 

[Tom Ogilvy]

Of course you could be referring to the values in the second column - which
are not tested. You can test them as well:

Private Sub ComboBoxgroupHSS_Change()

Dim c As Range
With UserFormDesign
.ListBoxHSS.Clear
For Each c In Range(.ComboBoxgroupHSS.Value).columns(1).Cells
If Len(Trim(c.Value)) > 0 and len(trim(c.offset(0,1))) > 0 Then
.ListBoxHSS.AddItem c.Value
.ListBoxHSS.List(.ListBoxHSS.Listcount - 1,1) =
C.offset(0,1).Value
end if
Next
End With