Blank item in listbox, how to remove
 

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

Blank item in listbox, how to remove
 

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

If cel < "" Then 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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Blank item in listbox, how to remove
 

On Jan 28, 12:01*pm, GS wrote:
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

* * * * If cel < "" Then 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

--
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

Blank item in listbox, how to remove
 

SS has brought this to us :
On Jan 28, 12:01*pm, GS wrote:
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

* * * * If cel < "" Then 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

--
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!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc