vbalistboxuserformlistboxitem

Userform Listbox selection & Value update based on column

I am creating an userform where user can select existing materials, and input stock quantity.

Process:

  1. Userform open
  2. User will select which stock column to input values (Stock1 ~ Stock10).
  3. User will select a material on the listbox.
  4. User can input stock quantity & select where the stock is from.
  5. When updating, the stock quantity will be added on the selected Stock column.

I got to the part on populating the listbox; and label showing selected material in the listbox to show name & color.

Stuck at: I am stuck on how to make:

Example: Below are the images of the example form & userform.

enter image description here

Thank you all in an advance.

Solution

  • If I understand you correctly ....

    enter image description here

    From the animation above, when two condition is met : the ListBox for name is selected (based on what user choose) AND the ComboBox for Stock is selected (based on what user choose), then :

    1. the cell where the user want to update the qty is activated
    2. the textbox for QTY is filled with the active cell value

    If the user change/update the value in the textbox for QTY then he click UPDATE button, the activecell value will be the textbox value.

    So if your case is similar with the animation above, then maybe you want to have a look the code below which maybe you can implement it to your case :

    Private Sub UserForm_Initialize()
Set rg = Range("A2", Range("A" & Rows.Count).End(xlUp))
Set rg = rg.Resize(rg.Rows.Count, 3)

'populate the ListBox for id, name and color
With ListNameColor
    .ColumnCount = 3
    .ColumnWidths = "0,40,40"
    .List = rg.Value
End With

'populate the combobox for stock
For i = 1 To 3: cbStock.AddItem "Stock" & i: Next

End Sub

Private Sub cbStock_Change()
If ListNameColor.ListIndex <> -1 Then Call PopQty
End Sub

Private Sub ListNameColor_Click()
If cbStock.ListIndex <> -1 Then Call PopQty
End Sub

Sub PopQty()
'get the row and column as r and c variable
r = Range("A2", Range("A" & Rows.Count).End(xlUp)).Find(ListNameColor.Value).Row
c = Rows(1).Find(cbStock.Value, lookat:=xlWhole).Column
Cells(r, c).Activate
tbQty.Value = ActiveCell.Value
End Sub

Private Sub btUpdate_Click()
ActiveCell.Value = tbQty.Value
End Sub

Private Sub btCancel_Click()
Unload Me
End Sub

    Please note that the item name in the combobox for stock must be exactly the same as the header name for the stock. For example : if in the header for stock the name is : STOCK-01, STOCK-02, STOCK-03, and so on, then when populating the combobox for stock must also with the same text.

    Debugging the PopQty sub :

    Sub PopQty()
Dim r As Integer: Dim c As Integer

'debugging
SelectedNameID = ListNameColor.Value 'is the SelectedNameID value correct in the Locals window?
'for example if the selected name is "bbb", then the value of SelectedNameID must be 2.

Set rgData = Range("A2", Range("A" & Rows.Count).End(xlUp))
rgData.Select 'is the selection correct ?
'it should select the "#" column from row 2 to the last row with number.

Set foundCell = rgData.Find(SelectedNameID)
'is the Locals window shows that the foundCell variable is NOT nothing ?

'if the foundCell variable in Locals window is not showing "Nothing" ....
foundCellRow = foundCell.Row 'is the foundCellRow value correct in the Locals window ?
'for example if the selected name is "bbb", then the foundCellRow value must be 3.

'get the row and column as r and c variable
r = Range("A2", Range("A" & Rows.Count).End(xlUp)).Find(ListNameColor.Value).Row
c = Rows(1).Find(cbStock.Value, lookat:=xlWhole).Column
Cells(r, c).Activate
tbQty.Value = ActiveCell.Value
End Sub