Updating an inventory using User Form

KTSARA

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

  • Service Registry.PNG
    Service Registry.PNG
    83.1 KB · Views: 15
  • SERVICE DETAILS USER FORM.PNG
    SERVICE DETAILS USER FORM.PNG
    7.7 KB · Views: 17
  • Inhouse Inventory.PNG
    Inhouse Inventory.PNG
    78.4 KB · Views: 14
Hello Dave,
I thought of it and deleted one row containing Lubricant oil.Now my work sheet has only two fields, but still updates only the first value. I will attach the full code for SubmitButton_Click function for your reference.
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 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)
    
With wsServiceRegistry
'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
.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 With

End If

End Sub
 
Upvote 0

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Hi,
about to head of out for morning

Rich (BB code):
Set updatecell = wsInhouseMaterialInventory.Cells(Me.InhouseMaterialComboBox.ListIndex + 2, 4)


Change the + value on the ListIndex to 2 & see if resolves

Dave
 
Upvote 0
just an oversight on my part.

Glad resolved

Dave
 
Upvote 0
Hello Dave,
Can I delete an entire row in the inventory, if the material quantity become zero after submiting this service details userform. I found the following code from internet, but I don't have an idea about where to insert it. I tried inserting it after the set updateCell statement, but it didn't work. If you could give me any insights on this I would really appreciate it.

Thanks a lot in advance
VBA Code:
Dim r As Long
    Dim LastRow As Long
    LastRow = Cells(Rows.Count, "D").End(xlUp).Row
    For r = LastRow To 1 Step -1
    If Cells(r, 1) = 0 Then
    Rows(r).Delete
    End If
    Next r
 
Upvote 0
Hi
After the updateCell line try adding following code shown in bold & see if does what you want

Rich (BB code):
updateCell.Value = updateCell.Value - Val(Me.MaterialQuantityTextBox.Value)

If updateCell.Value < 1 Then updateCell.EntireRow.Delete: Exit Sub


Dave
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

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