JHogan0721
New Member
- Joined
- Dec 7, 2016
- Messages
- 12
I need help creating an update button to update a listbox value. I have a listbox(ListBox1) that is populated via an array that contains 4 values - TextBox16, TextBox17, ComboBox3, and ComboBox4. I have a button that populates the listbox when pressed and another that recalls the values back to the appropriate boxes when clicked. I now need a button that updates the value in the listbox rather than creating a new entry.
Example:
I enter an item number (TextBox!6) and a quantity (TextBox17) and press a button. Those values then populate a 4 column list box. If a mistake was made in the quantity I want to be able to change it. I also would like to know how to make a delete button.
Because this userform is only needed to store data short term - it will be used to create a printable worksheet and then deleted minus a few pieces of information. I would like to use an array to avoid transferring very temp data to a worksheet and having to then delete it unless someone can suggest a streamlined way to do this. I am new to VBA and just learning on the fly. Any help is good help. I have attached current code below.
If you are going to give code suggestions - Please explain what you are doing. I want to learn not just copy and paste.
This is the code to add values to the listbox (to be honest I fully understand the loop but it works)
This is the code to recall the information
Again thanks to anyone who can help/explain.
Example:
I enter an item number (TextBox!6) and a quantity (TextBox17) and press a button. Those values then populate a 4 column list box. If a mistake was made in the quantity I want to be able to change it. I also would like to know how to make a delete button.
Because this userform is only needed to store data short term - it will be used to create a printable worksheet and then deleted minus a few pieces of information. I would like to use an array to avoid transferring very temp data to a worksheet and having to then delete it unless someone can suggest a streamlined way to do this. I am new to VBA and just learning on the fly. Any help is good help. I have attached current code below.
If you are going to give code suggestions - Please explain what you are doing. I want to learn not just copy and paste.
This is the code to add values to the listbox (to be honest I fully understand the loop but it works)
Code:
Private Sub CommandButton1_Click()
If TextBox17.Value = Trim("") Then
TextBox17.SetFocus
End If
If TextBox17.Value = Trim("") Then Exit Sub
On Error Resume Next
myarr = Array(TextBox16.Value, TextBox17.Value, ComboBox3.Value, ComboBox4.Value)
If ListBox1.ListCount <= 0 Then
ListBox1.Column = myarr
Else
ListBox1.AddItem myarr(0)
For n = 1 To 100
ListBox1.List(ListBox1.ListCount - 1, n) = myarr(n)
Next n
End If
End Sub
This is the code to recall the information
Code:
Private Sub CommandButton2_Click()
If ListBox1.ListIndex <> -1 Then
With ListBox1
TextBox16.Value = .List(.ListIndex, 0)
TextBox17.Value = .List(.ListIndex, 1)
End With
End If
End Sub
Again thanks to anyone who can help/explain.