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
Click to expand...
 
See inserted code below:




Hi,
I am using the following code to populate a listbox from a range.
Click to expand...
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.
Click to expand...
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?
Click to expand...
Private Sub UserForm_Initialize()
    AddStuff Range(Sheets("ToReview Pivot").[F8], Sheets("ToReview
Pivot").[F8].End(xlDown))
Click to expand...
End Sub
Click to expand...
Sub AddStuff(Data As Range)
    Dim d, cel As Range
    Set d = CreateObject("Scripting.Dictionary")
    For Each cel In Data
      On Error Resume Next
Click to expand...

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

--
Garry

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

- Show quoted text -
Click to expand...

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?
Click to expand...
Private Sub UserForm_Initialize()
    AddStuff Range(Sheets("ToReview Pivot").[F8], Sheets("ToReview
Pivot").[F8].End(xlDown))
Click to expand...
End Sub
Click to expand...
Sub AddStuff(Data As Range)
    Dim d, cel As Range
    Set d = CreateObject("Scripting.Dictionary")
    For Each cel In Data
      On Error Resume Next
Click to expand...

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

--
Garry

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

- Show quoted text -
Click to expand...

Works Great!! Thanks Much!!
-Steve
Click to expand...

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