Saturday, September 17, 2016

Excel Adding Item To Listbox And Combobox

Excel Vba From Textbox To Listbox & Combobox

           We conducted the processes without using the worksheet only on the userform in this sample.

With button on the userform, item can be added from textbox to combobox and to listbox .Also item in listbox can be updated and can be deleted with buton,

The listbox contains 13 column.Therefore we have used an array to fill the listbox :

"...
myarr = Array(cmbBtch.Value, txtBtchNo.Value, cmbSupCode.Value, txtSupName.Value, txtDate.Value, _
cmbItmCode.Value, txtItmName.Value, txtBox.Value, txtTara.Value, txtGwght.Value, txtTtara.Value, txtNwght.Value, txtPrice.Value)
lstStItems.ColumnCount = 13
If lstStItems.ListCount <= 0 Then
lstStItems.Column = myarr
Else
lstStItems.AddItem myarr(0)
For n = 1 To 12
lstStItems.List(lstStItems.ListCount - 1, n) = myarr(n)
Next n
...
"

With an other button ,text boxes and combo boxes can be filled with listbox selected item's value :
"
...
If lstStItems.ListIndex <> -1 Then
        With lstStItems
        cmbBtch.Value = .List(.ListIndex, 0)
        txtBtchNo.Value = .List(.ListIndex, 1)
        cmbSupCode.Value = .List(.ListIndex, 2)
        txtSupName.Value = .List(.ListIndex, 3)
        txtDate.Value = .List(.ListIndex, 4)
        cmbItmCode.Value = .List(.ListIndex, 5)
        txtItmName.Value = .List(.ListIndex, 6)
        txtBox.Value = .List(.ListIndex, 7)
        txtTara.Value = .List(.ListIndex, 8)
        txtGwght.Value = .List(.ListIndex, 9)
        txtTtara.Value = .List(.ListIndex, 10)
        txtNwght.Value = .List(.ListIndex, 11)
        txtPrice.Value = .List(.ListIndex, 12)
            
        End With
        Else
       MsgBox " Any listbox item isn't selected !", vbCritical, ""
    End If
...
"

2 comments:

  1. THIS IS AN AWESOME TUTORIAL... WHO EVER NEW OR JUST GETTING USED TO EXCEL VBA WILL FIND THIS ONE AS A TREASURE. THANK YOU VERY MUCH SIR FOR YOUR SERVICE,

    ReplyDelete
  2. This is a great bit of code here. Exactly what I was looking for. I wish you would have posted code for the entire tutorial. I am new and looking for the update button code.

    ReplyDelete