Modify the value of a listbox
Solved
leyjah
Posted messages
16
Status
Member
-
pijaku Posted messages 13513 Registration date Status Moderator Last intervention -
pijaku Posted messages 13513 Registration date Status Moderator Last intervention -
Hello,
I am seeking your help because I have a small issue with my app.
Here's what it is:
1. I have 2 userforms: CLIENTS and modification
2. In CLIENTS, I have three list boxes, zn1 (products), zn2 (quantity), zn3 (amount), and a command button (MODIFY).
3. Thanks to MODIFIER, the selected values in zn1, 2, and 3 are transferred to text boxes in the modification userform.
CODE:
Private Sub CommandButton1_Click()
modification.zdt1 = zn1.Value
modification.zdt2 = zn2.Value
modification.zdt3 = zn3.Value
modification.Show
End Sub
What I would like is that:
if I modify the values entered in the text boxes (zdt1, 2, and 3) of modification, they are updated in the list boxes (zn1, 2, and 3) of CLIENTS.
TEST CODE:
Private Sub CommandButton1_Click()
CLIENTS.zn1.RemoveItem
CLIENTS.zn2.RemoveItem
CLIENTS.zn3.RemoveItem
CLIENTS.zn1.AddItem zdt1.Value
CLIENTS.zn2.AddItem zdt2.Value
For i = 2 To 46
If zdt1.Value = Cells(i, 1) Then
CLIENTS.zn3.AddItem (zdt2.Value * Cells(i, 3))
End If
Next
modification.Hide
End Sub
THANK YOU FOR ANY HELP YOU CAN PROVIDE
REGARDS.
Configuration: Windows 7 / Firefox 20.0
I am seeking your help because I have a small issue with my app.
Here's what it is:
1. I have 2 userforms: CLIENTS and modification
2. In CLIENTS, I have three list boxes, zn1 (products), zn2 (quantity), zn3 (amount), and a command button (MODIFY).
3. Thanks to MODIFIER, the selected values in zn1, 2, and 3 are transferred to text boxes in the modification userform.
CODE:
Private Sub CommandButton1_Click()
modification.zdt1 = zn1.Value
modification.zdt2 = zn2.Value
modification.zdt3 = zn3.Value
modification.Show
End Sub
What I would like is that:
if I modify the values entered in the text boxes (zdt1, 2, and 3) of modification, they are updated in the list boxes (zn1, 2, and 3) of CLIENTS.
TEST CODE:
Private Sub CommandButton1_Click()
CLIENTS.zn1.RemoveItem
CLIENTS.zn2.RemoveItem
CLIENTS.zn3.RemoveItem
CLIENTS.zn1.AddItem zdt1.Value
CLIENTS.zn2.AddItem zdt2.Value
For i = 2 To 46
If zdt1.Value = Cells(i, 1) Then
CLIENTS.zn3.AddItem (zdt2.Value * Cells(i, 3))
End If
Next
modification.Hide
End Sub
THANK YOU FOR ANY HELP YOU CAN PROVIDE
REGARDS.
Configuration: Windows 7 / Firefox 20.0
5 answers
-
Hello,
Here’s how I would proceed:
1- Creating a standard module
2- In this module, declare 3 public variables:Public Var1 As String Public Var2 As String Public Var3 As String
3- The code for the CLIENTS userform button becomes:Private Sub CommandButton1_Click() Var1 = zn1.Value Var2 = zn2.Value Var3 = zn3.Value With modification .zdt1 = zn1.Value .zdt2 = zn2.Value .zdt3 = zn3.Value .Show End With End Sub
4- The code for the "Modify" button in the "modification" userform:Private Sub CommandButton1_Click() Dim i As Integer With CLIENTS For i = 0 To .zn1.ListCount - 1 If .zn1.List(i) = Var1 Then .zn1.List(i) = zdt1.Value Next i For i = 0 To .zn2.ListCount - 1 If .zn2.List(i) = Var2 Then .zn2.List(i) = zdt2.Value Next i For i = 0 To .zn3.ListCount - 1 If .zn3.List(i) = Var3 Then .zn3.List(i) = zdt3.Value Next i modification.Hide End With End Sub
5- Optional: it may be useful to prevent input in the textboxes if an item already exists in the listboxes... Or not!
Best regards,
Franck P -
-
I still have a small problem that persists:
at this level:
Private Sub CommandButton1_Click()
Dim i As Integer
With CLIENTS
For i = 0 To .zn1.ListCount - 1
If .zn1.List(i) = Var1 Then .zn1.List(i) = zdt1.Value
Next i
For i = 0 To .zn2.ListCount - 1
If .zn2.List(i) = Var2 Then .zn2.List(i) = zdt2.Value
Next i
For i = 0 To .zn3.ListCount - 1
If .zn3.List(i) = Var3 Then .zn3.List(i) = zdt3.Value
Next i
modification.Hide
End With
zdt3.value generally contains a product (the value of zdt2 * a value present in a cell of my worksheet).
Can you shed some light on this for me please?
Best regards-
Hello,
Once zn2 is modified, simply multiply its value by the value contained in the cell.
Private Sub CommandButton1_Click() Dim i As Integer With CLIENTS For i = 0 To .zn1.ListCount - 1 If .zn1.List(i) = Var1 Then .zn1.List(i) = zdt1.Value Next i For i = 0 To .zn2.ListCount - 1 If .zn2.List(i) = Var2 Then .zn2.List(i) = zdt2.Value .zn3.List(i) = zdt2.Value * Sheets("Machin").Range("Truc") End If Next i modification.Hide End With
-
-
http://cjoint.com/data/0Dto1NU7Kbu.htm
this way you might understand better what I want because this:
.zn3.List(i) = zdt2.Value * Sheets("Machin").Range("Truc")
will not work since the cells vary depending on the selected product.
Regards
Leyjah -
Indeed.
Then you must:
- insert a module (Insert/module under VBE)
place these declarations:Public Var1 As String Public Var2 As String Public Var3 As String
2- the Modify button of the Clients UserForm becomes:Private Sub CommandButton1_Click() Var1 = zn1.Value Var2 = zn2.Value Var3 = zn3.Value With modification .zdt1 = zn1.Value .zdt2 = zn2.Value .zdt3 = zn3.Value .Show End With End Sub
3- the Modify button of the modifications UserForm becomes:Private Sub CommandButton1_Click() Dim i As Integer, Prix As Double, Trouve As Range 'searches for the cell in column A containing the selection in the list zn1 With Sheets("Feuil1") Set Trouve = .Columns(1).Cells.Find(Var1, lookat:=xlWhole) End With 'if found then store in price the content of the corresponding C column, otherwise do nothing If Not Trouve Is Nothing Then Prix = Trouve.Offset(0, 2).Value Else Exit Sub With CLIENTS For i = 0 To .zn1.ListCount - 1 If .zn1.List(i) = Var1 Then .zn1.List(i) = zdt1.Value Next i For i = 0 To .zn2.ListCount - 1 If .zn2.List(i) = Var2 Then .zn2.List(i) = zdt2.Value .zn3.List(i) = zdt2.Value * Prix End If Next i modification.Hide End With End Sub
Your example file in return
--
Best regards,
Franck P
