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:

• selected stock column to be input column. *this is the part which user will use combobox to select available stock column on the left chart.
• selected listbox material to be input row.

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

• Left image is where the stock data will be input (Stock1 ~ Stock3).
• Right image is the userform; user will follow the process above; and it will be entered into the left chart.

Thank you all in an advance.

CodePudding user response：

If I understand you correctly ....

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.