Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
listbox remove item according to the value | Excel Programming | |||
remove items from listbox together with add item | Excel Programming | |||
Remove Item from Listbox by Item Name | Excel Programming | |||
remove item fr listbox (correction) | Excel Programming | |||
listbox remove Item | Excel Programming |