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
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,412
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:

jackt05

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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,412
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.
 

jackt05

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

Watch MrExcel Video

Forum statistics

Threads
1,108,630
Messages
5,523,991
Members
409,554
Latest member
denistrevisan

This Week's Hot Topics

Top