| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pick Several, Add All, Remove All
I've been searching quite a number of excel sites but haven't found
what I'm looking for: The standard dialogbox (similar to the box you get when you click on the 'To' button in Outlook|New Message). The box has 2 picking lists: - left - not chosen yet - right - chosen items Clicking on an item in either list will transfer the item to the other list. In addition there are the 'Add All' and 'Remove All' buttons, that respectively add all items from the left list to the right, and remove all all items from the right list to reinstate them in the left. I suppose there are numerous ways to manage this and am looking at: - An adaptation of J Walkenbach's GetOption - The, for me brandnew, Dictionary-object Should anyone have a pertinent idea how to tackle this issue, or for that matter know of a existing template, I'm all ear. On the other hand, should this really be something new and should you want to have a copy of the final VBA, just drop me a line. Leo |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pick Several, Add All, Remove All
I would maintain two listboxes (actually I use the listview controls in
report view - they are sexier) The buttons would perform the moving between. Or if you want to get really smart you could play with the dragdrop facilities. "Leo Elbertse" wrote in message ... I've been searching quite a number of excel sites but haven't found what I'm looking for: The standard dialogbox (similar to the box you get when you click on the 'To' button in Outlook|New Message). The box has 2 picking lists: - left - not chosen yet - right - chosen items Clicking on an item in either list will transfer the item to the other list. In addition there are the 'Add All' and 'Remove All' buttons, that respectively add all items from the left list to the right, and remove all all items from the right list to reinstate them in the left. I suppose there are numerous ways to manage this and am looking at: - An adaptation of J Walkenbach's GetOption - The, for me brandnew, Dictionary-object Should anyone have a pertinent idea how to tackle this issue, or for that matter know of a existing template, I'm all ear. On the other hand, should this really be something new and should you want to have a copy of the final VBA, just drop me a line. Leo |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pick Several, Add All, Remove All
Is this on a userform?
If yes, then this might work ok for you. I created a userform with two listboxes (listbox1 and listbox2) and four commandbuttons: BTN_moveAllLeft BTN_moveAllRight BTN_MoveSelectedLeft BTN_MoveSelectedRight And behind the userform, I had this code: Option Explicit Private Sub BTN_moveAllLeft_Click() Dim iCtr As Long For iCtr = 0 To Me.ListBox2.ListCount - 1 Me.ListBox1.AddItem Me.ListBox2.List(iCtr) Next iCtr Me.ListBox2.Clear End Sub Private Sub BTN_moveAllRight_Click() Dim iCtr As Long For iCtr = 0 To Me.ListBox1.ListCount - 1 Me.ListBox2.AddItem Me.ListBox1.List(iCtr) Next iCtr Me.ListBox1.Clear End Sub Private Sub BTN_MoveSelectedLeft_Click() Dim iCtr As Long For iCtr = 0 To Me.ListBox2.ListCount - 1 If Me.ListBox2.Selected(iCtr) = True Then Me.ListBox1.AddItem Me.ListBox2.List(iCtr) End If Next iCtr For iCtr = Me.ListBox2.ListCount - 1 To 0 Step -1 If Me.ListBox2.Selected(iCtr) = True Then Me.ListBox2.RemoveItem iCtr End If Next iCtr End Sub Private Sub BTN_MoveSelectedRight_Click() Dim iCtr As Long For iCtr = 0 To Me.ListBox1.ListCount - 1 If Me.ListBox1.Selected(iCtr) = True Then Me.ListBox2.AddItemme.ListBox1.List (iCtr) End If Next iCtr For iCtr = Me.ListBox1.ListCount - 1 To 0 Step -1 If Me.ListBox1.Selected(iCtr) = True Then Me.ListBox1.RemoveItem iCtr End If Next iCtr End Sub Private Sub UserForm_Initialize() Dim iCtr As Long With Me.ListBox1 For iCtr = 1 To 10 .AddItem "This is a test" & iCtr Next iCtr End With With Me.ListBox2 For iCtr = 1 To 10 .AddItem "This is a not a test" & iCtr Next iCtr End With Me.ListBox1.MultiSelect = fmMultiSelectMulti Me.ListBox2.MultiSelect = fmMultiSelectMulti End Sub Leo Elbertse wrote: I've been searching quite a number of excel sites but haven't found what I'm looking for: The standard dialogbox (similar to the box you get when you click on the 'To' button in Outlook|New Message). The box has 2 picking lists: - left - not chosen yet - right - chosen items Clicking on an item in either list will transfer the item to the other list. In addition there are the 'Add All' and 'Remove All' buttons, that respectively add all items from the left list to the right, and remove all all items from the right list to reinstate them in the left. I suppose there are numerous ways to manage this and am looking at: - An adaptation of J Walkenbach's GetOption - The, for me brandnew, Dictionary-object Should anyone have a pertinent idea how to tackle this issue, or for that matter know of a existing template, I'm all ear. On the other hand, should this really be something new and should you want to have a copy of the final VBA, just drop me a line. Leo -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pick Several, Add All, Remove All
And change this line:
Me.ListBox2.AddItemme.ListBox1.List (iCtr) to Me.ListBox2.AddItem Me.ListBox1.List(iCtr) (One mass change before sending was a mistake!) Dave Peterson wrote: Is this on a userform? If yes, then this might work ok for you. I created a userform with two listboxes (listbox1 and listbox2) and four commandbuttons: BTN_moveAllLeft BTN_moveAllRight BTN_MoveSelectedLeft BTN_MoveSelectedRight And behind the userform, I had this code: Option Explicit Private Sub BTN_moveAllLeft_Click() Dim iCtr As Long For iCtr = 0 To Me.ListBox2.ListCount - 1 Me.ListBox1.AddItem Me.ListBox2.List(iCtr) Next iCtr Me.ListBox2.Clear End Sub Private Sub BTN_moveAllRight_Click() Dim iCtr As Long For iCtr = 0 To Me.ListBox1.ListCount - 1 Me.ListBox2.AddItem Me.ListBox1.List(iCtr) Next iCtr Me.ListBox1.Clear End Sub Private Sub BTN_MoveSelectedLeft_Click() Dim iCtr As Long For iCtr = 0 To Me.ListBox2.ListCount - 1 If Me.ListBox2.Selected(iCtr) = True Then Me.ListBox1.AddItem Me.ListBox2.List(iCtr) End If Next iCtr For iCtr = Me.ListBox2.ListCount - 1 To 0 Step -1 If Me.ListBox2.Selected(iCtr) = True Then Me.ListBox2.RemoveItem iCtr End If Next iCtr End Sub Private Sub BTN_MoveSelectedRight_Click() Dim iCtr As Long For iCtr = 0 To Me.ListBox1.ListCount - 1 If Me.ListBox1.Selected(iCtr) = True Then Me.ListBox2.AddItemme.ListBox1.List (iCtr) End If Next iCtr For iCtr = Me.ListBox1.ListCount - 1 To 0 Step -1 If Me.ListBox1.Selected(iCtr) = True Then Me.ListBox1.RemoveItem iCtr End If Next iCtr End Sub Private Sub UserForm_Initialize() Dim iCtr As Long With Me.ListBox1 For iCtr = 1 To 10 .AddItem "This is a test" & iCtr Next iCtr End With With Me.ListBox2 For iCtr = 1 To 10 .AddItem "This is a not a test" & iCtr Next iCtr End With Me.ListBox1.MultiSelect = fmMultiSelectMulti Me.ListBox2.MultiSelect = fmMultiSelectMulti End Sub Leo Elbertse wrote: I've been searching quite a number of excel sites but haven't found what I'm looking for: The standard dialogbox (similar to the box you get when you click on the 'To' button in Outlook|New Message). The box has 2 picking lists: - left - not chosen yet - right - chosen items Clicking on an item in either list will transfer the item to the other list. In addition there are the 'Add All' and 'Remove All' buttons, that respectively add all items from the left list to the right, and remove all all items from the right list to reinstate them in the left. I suppose there are numerous ways to manage this and am looking at: - An adaptation of J Walkenbach's GetOption - The, for me brandnew, Dictionary-object Should anyone have a pertinent idea how to tackle this issue, or for that matter know of a existing template, I'm all ear. On the other hand, should this really be something new and should you want to have a copy of the final VBA, just drop me a line. Leo -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pick Several, Add All, Remove All
Dave and Rob,
Thanks very much! I've copied Dave's code lock, stock and barrel. All I have to do now is populate the listboxes correctly. Again thanks, Leo On Mon, 29 Dec 2003 18:30:51 -0600, Dave Peterson wrote: And change this line: Me.ListBox2.AddItemme.ListBox1.List (iCtr) to Me.ListBox2.AddItem Me.ListBox1.List(iCtr) (One mass change before sending was a mistake!) Dave Peterson wrote: Is this on a userform? If yes, then this might work ok for you. I created a userform with two listboxes (listbox1 and listbox2) and four commandbuttons: BTN_moveAllLeft BTN_moveAllRight BTN_MoveSelectedLeft BTN_MoveSelectedRight And behind the userform, I had this code: Option Explicit Private Sub BTN_moveAllLeft_Click() Dim iCtr As Long For iCtr = 0 To Me.ListBox2.ListCount - 1 Me.ListBox1.AddItem Me.ListBox2.List(iCtr) Next iCtr Me.ListBox2.Clear End Sub Private Sub BTN_moveAllRight_Click() Dim iCtr As Long For iCtr = 0 To Me.ListBox1.ListCount - 1 Me.ListBox2.AddItem Me.ListBox1.List(iCtr) Next iCtr Me.ListBox1.Clear End Sub Private Sub BTN_MoveSelectedLeft_Click() Dim iCtr As Long For iCtr = 0 To Me.ListBox2.ListCount - 1 If Me.ListBox2.Selected(iCtr) = True Then Me.ListBox1.AddItem Me.ListBox2.List(iCtr) End If Next iCtr For iCtr = Me.ListBox2.ListCount - 1 To 0 Step -1 If Me.ListBox2.Selected(iCtr) = True Then Me.ListBox2.RemoveItem iCtr End If Next iCtr End Sub Private Sub BTN_MoveSelectedRight_Click() Dim iCtr As Long For iCtr = 0 To Me.ListBox1.ListCount - 1 If Me.ListBox1.Selected(iCtr) = True Then Me.ListBox2.AddItemme.ListBox1.List (iCtr) End If Next iCtr For iCtr = Me.ListBox1.ListCount - 1 To 0 Step -1 If Me.ListBox1.Selected(iCtr) = True Then Me.ListBox1.RemoveItem iCtr End If Next iCtr End Sub Private Sub UserForm_Initialize() Dim iCtr As Long With Me.ListBox1 For iCtr = 1 To 10 .AddItem "This is a test" & iCtr Next iCtr End With With Me.ListBox2 For iCtr = 1 To 10 .AddItem "This is a not a test" & iCtr Next iCtr End With Me.ListBox1.MultiSelect = fmMultiSelectMulti Me.ListBox2.MultiSelect = fmMultiSelectMulti End Sub Leo Elbertse wrote: I've been searching quite a number of excel sites but haven't found what I'm looking for: The standard dialogbox (similar to the box you get when you click on the 'To' button in Outlook|New Message). The box has 2 picking lists: - left - not chosen yet - right - chosen items Clicking on an item in either list will transfer the item to the other list. In addition there are the 'Add All' and 'Remove All' buttons, that respectively add all items from the left list to the right, and remove all all items from the right list to reinstate them in the left. I suppose there are numerous ways to manage this and am looking at: - An adaptation of J Walkenbach's GetOption - The, for me brandnew, Dictionary-object Should anyone have a pertinent idea how to tackle this issue, or for that matter know of a existing template, I'm all ear. On the other hand, should this really be something new and should you want to have a copy of the final VBA, just drop me a line. Leo -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| remove convert/extract the number from'12345.56; ie remove ' sign | Excel Worksheet Functions | |||
| How do I remove hyperlink if 'remove' option is disabled | New Users to Excel | |||
| Making pick list conditional on selection from previous pick list | Excel Discussion (Misc queries) | |||
| Is there a way to pick the cells to add using sum? | Excel Worksheet Functions | |||
| Is there a way to remove numbers w/o remove formulas | Excel Programming | |||
Hybrid Mode
