Listbox populating progmatically

[Rick S.]

I have this list (for a listbox), condensed for this query..
'=====================
Private Sub UserForm_Initialize()
Me.ListBox1.AddItem ("1")
Me.ListBox1.AddItem ("2")
Me.ListBox1.AddItem ("3")
Me.ListBox1.AddItem ("4")
Me.ListBox1.AddItem ("5")
End Sub
'================

How do I read from that list and create code to mimick the following.
'==============
With ListBox1
If .Value = "1" Then
If .Value = "2" Then
If .Value = "3" Then
If .Value = "4" Then
If .Value = "5" Then
End If
End If
End If
End If
End If
'some code goes here
End With
'===============

I would like to prevent constant editing of the macro to accomidate new
entries.

--
Regards

Rick
XP Pro
Office 2007

 

[Bob Phillips]

Look at ListIndex in VBA help, it should give you what you want

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick S.]

Thank you Bob, I can now Add and/or Remove Items from my Listbox!
What I am failing to see is how to append, add or remove the Items to my
hard coded list. I have never tried to write code into a macro before and I
am not finding information "How to", I may not know what to search for?
Below is my list that I need to append, add or remove lines from.

'==========
Private Sub UserForm_Initialize()
Me.ListBox1.AddItem ("1")
Me.ListBox1.AddItem ("2")
Me.ListBox1.AddItem ("3")
Me.ListBox1.AddItem ("4")
Me.ListBox1.AddItem ("5")
End Sub
'==========

I am not looking just for "The Code"; direction like you have provided is
allways welcome!

--
Regards

Rick
XP Pro
Office 2007

 

[Bob Phillips]

Rick,

You have clearly demonstrated that you know how to add the item.

If you looked at ListIndex in help you should have gathered that Listindex
points at the item in the list that is selected. pair this with the
RemoveItem method, and you can delete the selected item

With Me.ListBox1
.RemoveItem (.ListIndex)
End With


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dana DeLouis]

... I need to append, add or remove lines from.

Not sure it this is what you need.

Private Sub UserForm_Initialize()
'// Add
Me.ListBox1.List = Array(1, 2, 3, 4, 5)
'// Delete
Me.ListBox1.RemoveItem (0) 'Remove 1st item
'//Append
Me.ListBox1.AddItem 11
End Sub

 

[Rick S.]

I will gather that due to my novice programming I did not explain my self
propperly. I do now understand how to add or remove an item from the listbox
per your initial and helpfull advice. What I am attempting now is to add or
append to a typed list within the macro and not within the listbox.

By playing with AddItem and RemoveItem I noticed when I refresh the listbox
my original list reapears and any added items do not or removed items have
returned due to Userform_Intialize commands.
Me.ListBox1.AddItem ("1")
Me.ListBox1.AddItem ("2")
etc. etc.

--
Regards

Rick
XP Pro
Office 2007

 

[Bob Phillips]

By refresh, do you mean that when you restart the form? This would be
because you reload it on re-showing the form.

Instead of unloading the form, just hid it, the Initialize event doesn't
fire then.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick S.]

I see what you mean by hiding the form.
From all the posts I have read most users have data on a worksheet and
read/load that into the listbox. I have not found a way to read the list
from within the macro itself, maybe its just to difficult to manage?
What I am going to do is try and use a hidden worksheet with the list stored
in cells.

Bob, thanks for your help! You have been patient and helpful at the same
time.

--
Regards

Rick
XP Pro
Office 2007

 

[Bob Phillips]

Rick,

People often bind the worksheet data to the listbox (using ListFillRange or
RowSource depending on the container), and then when the worksheet changes
the Listbox reflects it. Personally, I do not like that, I prefer to control
the Listbox fully myself. Reading the list from within the macro is simple
enough (presuming that I understand what you mean), something like

For Each cell In Worksheets("Sheet1").Range("A1:A10")
Me.ListBox1.AddItem cell.Value
Next cell

If You remove an item from the Listbox and you want adjust the worksheet to
reflect this just use

With Worksheets("Sheet1")
.remove the item from the listbox
.Rows(Me.ListBox.ListIndex).Delete
End With


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)