ListBox Userform add, edit, save help.

**calvinle** · 08-03-2014, 12:10 AM

Hi guys,

This seems a bit complicated, but I hope someone can help me. I figure out a way to organise my data which is to use Define Name. Because my data is 2 columns, 1st column is ID of the product, and 2nd colunm is the price to it. And these items are associated to an ID # which is the Define Name.

I have multiple request in here.

1) I want to be able to display the rowsource in the listbox1 from the choice made from combobox1.

2) If the inventory has been changed, then the user can choose to edit that ID#. In that edit mode, the user can choose to edit directly from the listbox (if possible because it's faster), or have to edit 1 by 1 with a textbox example. Also, if the user decide to add more ID product, it will be added to the next data of the same define name.

3) If the ID# is not available in the inventory, then the user can choose to add new inventory. This will add a new define name and a new data will be be store to the next available rows in the spreadsheet.

Finally, the spreadsheet is used to store the data, so I don't want the user with less knowledge to manipulate the whole inventory. That is why I prefer them to use the userform instead, more friendly.

Here is the template of the userform to start with:
listbox_sample.xlsm

Please help anyone.

Thank you.!!

**john55** · 08-04-2014, 06:01 AM

of course there many approaches, but as a start you can try this:
-you need two listboxes on yr UserForm, Listbox1 and Listbox2 and a ComboBox
-in Sheet2 add the first range, let's say range A (yr first range)
-in Sheet3 add the 2nd range, lets' say range B (yr 2nd range)
this is the code for ComboBox1

Private Sub ComboBox1_Change()
Select Case ComboBox1.Value
Case Is = "A"
ListBox1.Visible = True
ListBox2.Visible = False
Me.ListBox1.RowSource = "Sheet2!A1:B" & Sheet2.Cells(Rows.Count, "B").End(xlUp).Row
Case Is = "B"
ListBox1.Visible = False
ListBox2.Visible = True
Me.ListBox2.RowSource = "Sheet3!A1:B" & Sheet3.Cells(Rows.Count, "B").End(xlUp).Row
End Select
End Sub

this is for UserForm

Private Sub UserForm_Initialize()
   ComboBox1.List = Array("A", "B") 'change for yr needs
End Sub

now you need the code for adding item in range A or B and edit what you want, it's some work for it....
hope it helps you or it gives a right direction?!

**john55** · 08-04-2014, 03:38 PM

add item in ListBox1 and 2

Private Sub CommandButton5_Click()
'ListBox1.AddItem TextBox1/2.Value
 Dim NextRw As Long
   With Sheet2    'find next empty row using Column A
        NextRw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        .Cells(NextRw, 1).Value = Me.TextBox1.Value
        .Cells(NextRw, 2).Value = Me.TextBox2.Value
        End With
Unload Me
UserForm1.Show
End Sub

Private Sub CommandButton6_Click()
'ListBox2.AddItem TextBox1/2.Value
 Dim NextRw As Long
   With Sheet3    'find next empty row using Column A
        NextRw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        .Cells(NextRw, 1).Value = Me.TextBox1.Value
        .Cells(NextRw, 2).Value = Me.TextBox2.Value
        End With
Unload Me
UserForm1.Show
End Sub

Private Sub UserForm_Initialize()
   ComboBox1.List = Array("A", "B")
   ListBox1.Visible = False
   ListBox2.Visible = False
   CommandButton5.Visible = False 'add record to listbox1
   CommandButton6.Visible = False 'add record to listbox2
   
End Sub

and so on...