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

jackt05

Board Regular
Joined
Feb 1, 2009
Messages
194
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Try this, with the userform ComboBoxes named cboColumnHeaders and cboStockItem, the TextBox named txtQuantity and the CommandButton named cmdSubmit.

Code:
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.
 
Last edited:
Upvote 0
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
 
Upvote 0
Change cmdSubmit_Click to:
Code:
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:
Code:
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top