UserForm ListBox save data and edit
Solved
trevsiob
Posted messages
11
Registration date
Status
Member
Last intervention
-
pijaku Posted messages 13513 Registration date Status Moderator Last intervention -
pijaku Posted messages 13513 Registration date Status Moderator Last intervention -
Hello,
I have a UserForm with 4 ListBoxes. Users must select items in the first ListBox, press an "ADD" button, and the item is then transferred to the second ListBox. They can also select an item that has previously been sent to ListBox 2 and press a "REMOVE" button to take the item out (in case of an error, for example). They must do the same with the third ListBox, which contains a different list of items, and by selecting them, they are transferred to the fourth ListBox under the same principle described above. They must finally press a "FINISH" button that closes the UserForm and transfers the selection to two different ListBoxes on the Excel sheet. On this sheet, there is a "MODIFY" button that allows users to reopen the UserForm, which appears with the previous selection, and they can modify their work. This entire process works well but I have two problems. First, I would like to be able to save the ListBox data from the Excel sheet when the file is closed. To do this, I use a range of cells into which the contents of the two ListBoxes are transferred, and I wrote code in Workbook_Open that checks the range and uses it to put the items back into the ListBoxes, but I cannot modify the selection when the items come from the range. Secondly, I would like users not to be able to choose the same item twice in the UserForm. It would be necessary that when an item is transferred using the "ADD" button, the same item is removed or invisible or disabled from the first ListBox, and when the "REMOVE" button is pressed, the item returns to the first ListBox.
I hope I was clear enough!
Thank you very much
Alex
I have a UserForm with 4 ListBoxes. Users must select items in the first ListBox, press an "ADD" button, and the item is then transferred to the second ListBox. They can also select an item that has previously been sent to ListBox 2 and press a "REMOVE" button to take the item out (in case of an error, for example). They must do the same with the third ListBox, which contains a different list of items, and by selecting them, they are transferred to the fourth ListBox under the same principle described above. They must finally press a "FINISH" button that closes the UserForm and transfers the selection to two different ListBoxes on the Excel sheet. On this sheet, there is a "MODIFY" button that allows users to reopen the UserForm, which appears with the previous selection, and they can modify their work. This entire process works well but I have two problems. First, I would like to be able to save the ListBox data from the Excel sheet when the file is closed. To do this, I use a range of cells into which the contents of the two ListBoxes are transferred, and I wrote code in Workbook_Open that checks the range and uses it to put the items back into the ListBoxes, but I cannot modify the selection when the items come from the range. Secondly, I would like users not to be able to choose the same item twice in the UserForm. It would be necessary that when an item is transferred using the "ADD" button, the same item is removed or invisible or disabled from the first ListBox, and when the "REMOVE" button is pressed, the item returns to the first ListBox.
I hope I was clear enough!
Thank you very much
Alex
4 answers
-
I am resending the link from the first post:
http://www.webtransfer.ch/en/download/681f638314d6f969a10b6194ea81ea4238ea1d80 -
Hello,
For the second question, here is my answer in code:
'move from ListBox1 to ListBox2
Private Sub cmdadd_Click()
Dim counter As Integer
counter = -1
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
ListBox2.AddItem ListBox1.List(i)
counter = i
End If
Next i
If counter <> -1 Then ListBox1.RemoveItem (counter)
End Sub
'move from ListBox2 to ListBox1
Private Sub cmdremove_Click()
Dim counter As Integer
counter = -1
For i = 0 To ListBox2.ListCount - 1
If ListBox2.Selected(i) Then
ListBox1.AddItem ListBox2.List(i)
counter = i
End If
Next i
If counter <> -1 Then ListBox2.RemoveItem (counter)
End Sub
However, I did not understand the first question...
Sincerely,
Franck -
pijaku,
My first question was:
When the user has finished their work, they need to save the Excel file. But when reopening the saved file, Excel does not save the data from the listboxes. So I created a "Range" in which the selection is stored so that the data can be saved, and I have a code in WorkBook_Open that retrieves the data from the "Range" and populates the ListBox in the Excel sheet. This way, I do not lose my data. The problem is that if I want to modify the selection, it doesn't work...-
Here is your file in return.
I made quite a few modifications.
For question 1, you manually modify in your two Ranges and click on the button I added.
Otherwise, other modifications:
- replacement of the RowSource properties of the userform listboxes with the list properties to allow for deletion,
- multiple selection of listboxes 1 and 3 on the userform
and some odds and ends here and there, of no importance. -
-
-
-
-
This solution doesn't really work for me.
1. In fact, this whole process will be part of a form. The display must therefore be optimal and users won't be able to go into the range to make a manual change.
2. The code to make the items disappear after selection contains several bugs that I want to avoid. In fact, when testing, if several items are selected, they don’t all get deleted. Also, by choosing the multi-selection like you did, it becomes less interesting for users who will only choose one item and want to change it before clicking on "ADD".
If you have any other suggestions for tools (changing ListBox for something else, etc.), feel free to let me know.
Thank you.
