0

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.

enter image description here

Thank you all in an advance.

Improve this question
3
  • In the userform, is choosing Stock1 automatically change the "Stock From" to Market ? Choosing Stock3 automatically change the "Stock From" to Office ? Choosing Stock2 automatically change the "Stock From" to Warehouse? In other words : Stock1 means the stock from Market, Stock2 means the stock from Warehouse, Stock3 means the stock from Office. Please CMIIW. Commented Dec 8, 2022 at 2:39
  • @karma Yes, when selecting the 1st combobox (Stock1- Stock3), the 2nd Combobox will change Stock from:(market, warehouse, office). But I am mainly lost on how to enter data; to define where the data needs to input (row = listbox selection) & (column = 1st combobox value). Commented Dec 8, 2022 at 3:26
  • Please have a look on my answer. I think you can solve the problem when see the code, especially the code in the PopQty sub. Commented Dec 8, 2022 at 3:38

1 Answer 1

Reset to default
1

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
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Wow! It works fantastically! Thank you for guided steps :)
Have you tried to dim as integer ? dim r as integer: dim c as integer ---> I hope this work.
just now I put Dim r As Integer: Dim c As Integer right after Sub PopQty. No error on my side. Sorry I can't reproduce the error. If that's OK with you, maybe put the whole code for the Userform in your question ?
Please try to debug, put the red stopper in Sub PopQty, then press F8 to debug it and see the result in the Locals window. Please have a look in my edited answer. In the Sub PopQty, I break the code to some more variables.
Glad that finally you found the issue :). Thank you for updating it to me.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.