| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox populating progmatically
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Listbox populating progmatically
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." wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Listbox populating progmatically
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" wrote: 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." wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Listbox populating progmatically
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) "Rick S." wrote in message ... 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" wrote: 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." wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Listbox populating progmatically
... 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 -- Dana DeLoui "Rick S." wrote in message ... 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" wrote: 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." wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Listbox populating progmatically
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" wrote: 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) "Rick S." wrote in message ... 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" wrote: 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." wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Listbox populating progmatically
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." wrote in message ... 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" wrote: 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) "Rick S." wrote in message ... 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" wrote: 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." wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Listbox populating progmatically
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" wrote: 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." wrote in message ... 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" wrote: 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) "Rick S." wrote in message ... 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" wrote: 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." wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Listbox populating progmatically
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) "Rick S." wrote in message ... 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" wrote: 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." wrote in message ... 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" wrote: 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) "Rick S." wrote in message ... 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" wrote: 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." wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Populating listbox | Excel Discussion (Misc queries) | |||
| populating a listbox | Excel Programming | |||
| Populating listbox | Excel Programming | |||
| Populating a ListBox | Excel Programming | |||
| Populating a userform 3 col listbox | Excel Programming | |||
Linear Mode
