Updating an inventory using User Form

[KTSARA]

Hello everyone,
I am very new to VBA and I came accross this question for which I couldn't find an answer even after browsing the internet for hours. So, any help from experts like you all is highly appreciated.
I have two data sheets as Service Registry and Inhouse Inventory. There is a userform (ServiceDetailsUserForm) to feed data into Service Registry work sheet. All the materials in the inhouse inventory sheet are listed down in the MaterialComboBox in the ServiceDetailsUserForm. User have to select a material from the combo box and enter the quantity of material used for the service. After clicking the submit button on the user form, the material and the quantity used for the service should be entered to the Service registry work sheet and at the same time Inhouse inventry work sheet should be updated by subtracting the quantity of material used for the service from the quantity of the material which was available in the inventory.
Unfortunately I couldn't come up with a code which can even do half of the job, so I don't have a code to upload. Very sorry for that. Instead I will upload some pictures.

Thanks a lot in advance.

 

[dmt32]

Hi,
Have a look here:Excel UserForm With ComboBoxes
and see if this will help you. You can download workbook for free & maybe modify code to meet your specific need.

If still unable to resolve, post back code you have tried - plenty here to help

Dave

 

[KTSARA]

Hi Dave,
Thank you for taking time to answer my question. I have done the part where I send data to a work sheet using a user form. What I couldn't do was updating data in another sheet at the same time with the same information. I couldn't write a code for that. Anyway what I tried gives an error message saying that there is a syntax error. As I am very new to VBA I am stucked here without knowing how to correct it. Anyway I will post it below. Please kindly go through it and let me know of what I have done wrong. The code is a bit long, so please be patient with me. Any help from would be highly appreciated.
Thank you so much in advance.

Private Sub SubmitCommandButton_Click()

If Trim(MachineCodeComboBox.Value) = "" Then
        MachineCodeComboBox.SetFocus
        MsgBox "Enter the Machine Code"
    ElseIf Trim(ServiceDateTextBox.Value) = "DD/MM/YYYY" Then
       ServiceDateTextBox.SetFocus
        MsgBox "Enter the Service Date"
    ElseIf Trim(ServiceProviderComboBox.Value) = "" Then
        ServiceProviderComboBox.SetFocus
        MsgBox "Enter the Service Provider"
    ElseIf Trim(TechnicalPersonNameTextBox.Value) = "" Then
       TechnicalPersonNameTextBox.SetFocus
        MsgBox "Enter the name of the Technical Person"
    ElseIf Trim(PONumberTextBox.Value) = "" Then
       PONumberTextBox.SetFocus
        MsgBox "Enter the PO Number"
     ElseIf Trim(SupervisorTextBox.Value) = "" Then
       SupervisorTextBox.SetFocus
        MsgBox "Enter the Supervisor"
    ElseIf Trim(LastServiceDateTextBox.Value) = "DD/MM/YYYY" Then
       LastServiceDateTextBox.SetFocus
        MsgBox "Enter the Last Service Date"
    ElseIf Trim(NextServiceDateTextBox.Value) = "DD/MM/YYYY" Then
       NextServiceDateTextBox.SetFocus
        MsgBox "Enter the Next Service Date"
    
    
    Else
        

Dim emptyRow As Long

'Make Sheet4 active
Sheet4.Activate
 
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Transfer information
Cells(emptyRow, 1).Value = MachineCodeComboBox.Value
Cells(emptyRow, 2).Value = ServiceDateTextBox.Value
Cells(emptyRow, 3).Value = ServiceProviderComboBox.Value
Cells(emptyRow, 4).Value = TechnicalPersonNameTextBox.Value
Cells(emptyRow, 5).Value = PONumberTextBox.Value
Cells(emptyRow, 6).Value = CUSDECNumberTextBox.Value
Cells(emptyRow, 7).Value = SupervisorTextBox.Value
Cells(emptyRow, 8).Value = InhouseMaterialComboBox.Value
Cells(emptyRow, 9).Value = MaterialUOMTextBox.Value
Cells(emptyRow, 10).Value = MaterialQuantityTextBox.Value
Application.WorksheetFunction.VLookup(Me.InhouseMaterialComboBox, Sheet6.Range("B:D"), 3, False)- MaterialQuantityTextBox.Value = Application.WorksheetFunction.VLookup(Me.InhouseMaterialComboBox, Sheet6.Range("B:D"), 3, False)
Cells(emptyRow, 11).Value = PurchasedMaterialComboBox.Value
Cells(emptyRow, 12).Value = UOMComboBox.Value
Cells(emptyRow, 13).Value = QuantityTextBox.Value
Cells(emptyRow, 14).Value = ServiceProviderMaterialComboBox.Value
Cells(emptyRow, 15).Value = ServiceProviderUOMComboBox.Value
Cells(emptyRow, 16).Value = ServiceProviderQuantityTextBox.Value
Cells(emptyRow, 17).Value = ExtraMaterialComboBox.Value
Cells(emptyRow, 18).Value = ExtraMaterialUOMComboBox.Value
Cells(emptyRow, 19).Value = ExtraMaterialQuantityTextBox.Value
Cells(emptyRow, 20).Value = LastServiceDateTextBox.Value
Cells(emptyRow, 21).Value = NextServiceDateTextBox.Value



   End If

End Sub

 

[dmt32]

are you able to place copy of your workbook in a dropbox & place link to it here? Just makes it easier for forum to help with a solution

Dave

 

[KTSARA]

Hello Dave,
Here is the link of my work book. Isuue is with the service details user form.

user form.xlsm

Thank you

 

[dmt32]

ok will take a look.

 

[KTSARA]

Thanks a lot Dave for trying to help me. I wrote a code like below to send data to two sheets at the same time using a user form. This code sends data to Service Regidtry sheet, but the inhouse inventry is not updated. If you can help me with this code, it would be much appreciated.

This is what I am trying to do:

I want to add data to the next empty row in Service Registry sheet and at the same time should find the material name (user select this from a combo box in the userform) from the Inhouse Inventry and the quantity of that material should be updated when clicking the submit button.
For example: User select Battery water as the material and enter 5L to the quantity text box in the user form. When user click the submit button, I want to enter these data to service registry sheet and at the same time I want to find the Battery water from inhouse inventory and subtract 5L from the quantity of battery water available and update the quantity to the new value.

Thanks a lot in advance

Dim ws As Worksheet
Set ws = Worksheets("Service Registry")
Dim ws1 As Worksheet
Set ws1 = Worksheets("Inhouse Material Inventory")
Dim updateCell As Long
Dim emptyRow As Long


UpdateCell = Application.WorksheetFunction.VLookup(Me.InhouseMaterialComboBox, Sheet6.Range("B:D"), 3, False)
ws1.Cells(updateCell, 4).Value = Application.WorksheetFunction.VLookup(Me.InhouseMaterialComboBox, Sheet6.Range("B:D"), 3, False) - MaterialQuantityTextBox.Value
 
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Transfer information
ws.Cells(emptyRow, 1).Value = MachineCodeComboBox.Value
ws.Cells(emptyRow, 2).Value = ServiceDateTextBox.Value
ws.Cells(emptyRow, 3).Value = ServiceProviderComboBox.Value
ws.Cells(emptyRow, 4).Value = TechnicalPersonNameTextBox.Value
ws.Cells(emptyRow, 5).Value = PONumberTextBox.Value
ws.Cells(emptyRow, 6).Value = CUSDECNumberTextBox.Value
ws.Cells(emptyRow, 7).Value = SupervisorTextBox.Value
ws.Cells(emptyRow, 8).Value = InhouseMaterialComboBox.Value
ws.Cells(emptyRow, 9).Value = MaterialUOMTextBox.Value
ws.Cells(emptyRow, 10).Value = MaterialQuantityTextBox.Value

 

[dmt32]

Short of time at moment - untested but see if this updated to code you just posted helps

Dim wsServiceRegistry As Worksheet, wsInhouseMaterialInventory As Worksheet
    Dim updateCell As Range
    Dim emptyRow As Long
    
    With ThisWorkbook
        Set wsServiceRegistry = .Worksheets("Service Registry")
        Set wsInhouseMaterialInventory = .Worksheets("Inhouse Material Inventory")
    End With
    
    Set updateCell = wsInhouseMaterialInventory.Cells(Me.InhouseMaterialComboBox.ListIndex + 1, 4)
    
    updateCell.Value = updateCell.Value - Val(Me.MaterialQuantityTextBox.Value)
    
    
    
'Transfer information
    With wsServiceRegistry
'Determine emptyRow
        emptyRow = WorksheetFunction.CountA(.Range("A:A")) + 1
        .Cells(emptyRow, 1).Value = MachineCodeComboBox.Value
        .Cells(emptyRow, 2).Value = DateValue(ServiceDateTextBox.Value)
        .Cells(emptyRow, 3).Value = ServiceProviderComboBox.Value
        .Cells(emptyRow, 4).Value = TechnicalPersonNameTextBox.Value
        .Cells(emptyRow, 5).Value = PONumberTextBox.Value
        .Cells(emptyRow, 6).Value = CUSDECNumberTextBox.Value
        .Cells(emptyRow, 7).Value = SupervisorTextBox.Value
        .Cells(emptyRow, 8).Value = InhouseMaterialComboBox.Value
        .Cells(emptyRow, 9).Value = MaterialUOMTextBox.Value
        .Cells(emptyRow, 10).Value = MaterialQuantityTextBox.Value
    End With

Dave

 

[KTSARA]

Hello Dave,
Thanks again for trying to help. I tried your code. It sends data to the Service Registry sheet perfectly, but regardless of the material type entered by the user, it updates the quantity of the first material in the inventory list. I tried to change the code, but didn't get the expected result. If you could help me to correct this, it would be much appreciated.
Thanks a lot in advance

 

[dmt32]

Hi,
I suspect issue maybe that in your InhouseMaterialComboBox you have Material Type selection values that are the same - my suggested approach uses the listindex property of the control so it's important to select the correct item from the list to post updated value to the correct range.

I am working through your project with hopefully a solution to this & will post an update asap.

Dave

Book1
	A	B	C	D	E	F
1	#	Material Type	UOM	Quantity	Supplier	Date
2	1	Lubricant oil	Litre	12	dddddew	10/12/2019
3	2	Lubricant oil	Litre	10	yuymm	10/12/2019
4	3	Battery Water	Kilogram	12	nkjk	15/11/2018
INHOUSE MATERIAL INVENTORY