Blank item in listbox, how to remove

  • Thread starter Thread starter SS
  • Start date Start date
S

SS

Hi,
I am using the following code to populate a listbox from a range.

This works great, unless there is only one item in the range. When
there is only one item, that item shows in the listbox, but along with
a Blank item.
Any time there is more than one item in the range to populate, there
is no blank items brought into the listbox.

Do you know how i can fix this code to work correctly when only one
item is in the range.
Or, do you know of a better way to initialize the form lisbox using
the range, which starts at F8?


Private Sub UserForm_Initialize()
AddStuff Range(Sheets("ToReview Pivot").[F8], Sheets("ToReview
Pivot").[F8].End(xlDown))

End Sub

Sub AddStuff(Data As Range)
Dim d, cel As Range
Set d = CreateObject("Scripting.Dictionary")
For Each cel In Data
On Error Resume Next
d.Add cel.Text, cel.Text
Next
'ComboBox1.List() = d.items
ListBox1.List() = d.items

Me.ListBox1.MultiSelect = fmMultiSelectMulti
Me.ListBox2.MultiSelect = fmMultiSelectMulti


End Sub



Many Thanks,
Steve
 
See inserted code below:
Hi,
I am using the following code to populate a listbox from a range.

This works great, unless there is only one item in the range. When
there is only one item, that item shows in the listbox, but along with
a Blank item.
Any time there is more than one item in the range to populate, there
is no blank items brought into the listbox.

Do you know how i can fix this code to work correctly when only one
item is in the range.
Or, do you know of a better way to initialize the form lisbox using
the range, which starts at F8?


Private Sub UserForm_Initialize()
AddStuff Range(Sheets("ToReview Pivot").[F8], Sheets("ToReview
Pivot").[F8].End(xlDown))

End Sub

Sub AddStuff(Data As Range)
Dim d, cel As Range
Set d = CreateObject("Scripting.Dictionary")
For Each cel In Data
On Error Resume Next
 
See inserted code below:




Hi,
I am using the following code to populate a listbox from a range.
This works great, unless there is only one item in the range.  When
there is only one item, that item shows in the listbox, but along with
a Blank item.
Any time there is more than one item in the range to populate, there
is no blank items brought into the listbox.
Do you know how i can fix this code to work correctly when only one
item is in the range.
Or, do you know of a better way to initialize the form lisbox using
the range, which starts at F8?
Private Sub UserForm_Initialize()
    AddStuff Range(Sheets("ToReview Pivot").[F8], Sheets("ToReview
Pivot").[F8].End(xlDown))
Sub AddStuff(Data As Range)
    Dim d, cel As Range
    Set d = CreateObject("Scripting.Dictionary")
    For Each cel In Data
      On Error Resume Next

        If cel said:
    Next
    'ComboBox1.List() = d.items
    ListBox1.List() = d.items
    Me.ListBox1.MultiSelect = fmMultiSelectMulti
    Me.ListBox2.MultiSelect = fmMultiSelectMulti
Many Thanks,
Steve

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

Works Great!! Thanks Much!!
-Steve
 
SS has brought this to us :
See inserted code below:




Hi,
I am using the following code to populate a listbox from a range.
This works great, unless there is only one item in the range.  When
there is only one item, that item shows in the listbox, but along with
a Blank item.
Any time there is more than one item in the range to populate, there
is no blank items brought into the listbox.
Do you know how i can fix this code to work correctly when only one
item is in the range.
Or, do you know of a better way to initialize the form lisbox using
the range, which starts at F8?
Private Sub UserForm_Initialize()
    AddStuff Range(Sheets("ToReview Pivot").[F8], Sheets("ToReview
Pivot").[F8].End(xlDown))
Sub AddStuff(Data As Range)
    Dim d, cel As Range
    Set d = CreateObject("Scripting.Dictionary")
    For Each cel In Data
      On Error Resume Next

        If cel said:
    Next
    'ComboBox1.List() = d.items
    ListBox1.List() = d.items
    Me.ListBox1.MultiSelect = fmMultiSelectMulti
    Me.ListBox2.MultiSelect = fmMultiSelectMulti
End Sub
Many Thanks,
Steve

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

Works Great!! Thanks Much!!
-Steve

You're welcome! ..glad to help!
 
Back
Top