Updating an inventory using User Form

KTSARA

New Member
Joined
Nov 18, 2019
Messages
21
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.
 

Attachments

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,280
Office Version
2013
Platform
Windows
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

New Member
Joined
Nov 18, 2019
Messages
21
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.

VBA Code:
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

Well-known Member
Joined
Jul 3, 2012
Messages
5,280
Office Version
2013
Platform
Windows
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

New Member
Joined
Nov 18, 2019
Messages
21
Hello Dave,
Here is the link of my work book. Isuue is with the service details user form.

user form.xlsm

Thank you
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,280
Office Version
2013
Platform
Windows
ok will take a look.
 

KTSARA

New Member
Joined
Nov 18, 2019
Messages
21
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

VBA Code:
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

Well-known Member
Joined
Jul 3, 2012
Messages
5,280
Office Version
2013
Platform
Windows
Short of time at moment - untested but see if this updated to code you just posted helps

VBA Code:
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

New Member
Joined
Nov 18, 2019
Messages
21
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

Well-known Member
Joined
Jul 3, 2012
Messages
5,280
Office Version
2013
Platform
Windows
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
ABCDEF
1#Material TypeUOMQuantitySupplierDate
21Lubricant oilLitre12dddddew10/12/2019
32Lubricant oilLitre10yuymm10/12/2019
43Battery WaterKilogram12nkjk15/11/2018
INHOUSE MATERIAL INVENTORY
 

Forum statistics

Threads
1,082,323
Messages
5,364,579
Members
400,809
Latest member
formulasataglance

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top