Add stock qty entered in Userform, based on Selection in combobox

[jackt05]

Dear All Hi,

As I am new to coding and after searching the internet for the solution and not finding it I am posting this here as I have been helped here before.

I have a sheet of stock Outward, with the headers as: Column D=STM1; Column E=STM2 and on.

In A column I have the stock items Range A2:A34.
sensor; Plate; Battery; etc.

I want to create a userform on another dashboard sheet where anyone who uses any item from the stock will enter in the userform and the stock Qty used will be entered in the stock outward sheet (without he actually using the sheet to enter.

My concept is that in the first combobox there should be a auto populated list of Column headers STM1;STM2;etc. (how can I get this header in the combobox list).

Second combobox list will contain item names like sensor; Battery; etc.

Third textbox on the userform will be for qty no he has used.

Now when he submits the form the qty entered should be entered in the cell destination according to the above 2 comboboxes selection. that is if he selectes STM2 in first combobox and sensor in second combobox and writes 2 in the text box of qty the enter of the number 2 should be done in cell "E2" If E column header is STM2 and A2 has sensor in the item column.

Appreciate if anyone has any solution to this maybe this will help many as it may be a common requirement.

Thanks in Advance
Jack

 

[jackt05]

Dear All,

Can anyone suggest code for the above

Jack

 

[John_w]

Try this, with the userform ComboBoxes named cboColumnHeaders and cboStockItem, the TextBox named txtQuantity and the CommandButton named cmdSubmit.

Private Sub UserForm_Initialize()

    With Worksheets("Stock Outward")
        cboColumnHeaders.List = Application.Transpose(.Range("D1", .Cells(1, .Columns.Count).End(xlToLeft)).Value)
        cboStockItem.List = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Value
    End With
    
End Sub

Private Sub cmdSubmit_Click()

    Worksheets("Stock Outward").Range("D2").Offset(cboStockItem.ListIndex, cboColumnHeaders.ListIndex).Value = CLng(txtQuantity)

End Sub

Note you can either populate cboStockItem as shown above, or set its RowSource property at design time to 'Stock Outward'!A2:A34, however the code automatically expands the list if more stock items are added in A35, A36, etc.

 

[jackt05]

Thanks John it is working as expected.

Request you to add just two things to the code :

1) Check and warn if there is already something in that destination cell. It should record only if the destination cell is empty.
2) Launch the userform by a button on a sheet. and Close the userform after pressing submit button (With qty added Successfully Msg).

This forum has always helped me Thanks to such helpful persons who are in this forum.

Regards,
Jack

 

[John_w]

Change cmdSubmit_Click to:

Private Sub cmdSubmit_Click()

    If txtQuantity.Value <> "" Then
        With Worksheets("Stock Outward").Range("D2").Offset(cboStockItem.ListIndex, cboColumnHeaders.ListIndex)
            If IsEmpty(.Value) Then
                .Value = txtQuantity.Value
                Unload Me
                MsgBox "Quantity " & txtQuantity.Value & _
                    " added successfully to cell " & .Address(False, False) & " for Column Header " & cboColumnHeaders.List(cboColumnHeaders.ListIndex) & ", Stock Item " & cboStockItem.List(cboStockItem.ListIndex)
            Else
                MsgBox "Cell " & .Address(False, False) & _
                    " for Column Header " & cboColumnHeaders.List(cboColumnHeaders.ListIndex) & ", Stock Item " & cboStockItem.List(cboStockItem.ListIndex) & _
                    " is not empty"
            End If
        End With
    End If
    
End Sub

To launch the userform via a button, put this code in a standard module:

Public Sub Show_UserForm()

    Dim form As UserForm1
    Set form = New UserForm1
    form.Show
    
End Sub

And add a Button (Form Control) to the sheet and assign the Show_UserForm macro to the button.

 

[jackt05]

Hi John,

The code is working as desired I am thankful for your support and appreciate your time given. This is solved and completed.

Thanks to Excel Forum and Mr John.

Jack