I am very new to Excel VBA and any help from you is highly appreciated. I have two work sheets as Inhouse Material Inventory and Service Details. Inventory work sheet contains Materials available in our inhouse inventory and their available quantity. I am using a user form to feed data into the service details work sheet. There is a combo box in this user form to select the materials from inhouse inventory used for machine maintenance activities. There is a text box to enter the quantity of materials used for the machine maintenance. The value entered to this text box should be smaller than or equal to the material quantity available in the inhouse inventory. If a value greater than inhouse quantity is entered an error message should be generated. Here is my code for the Service Details User Form which I developped using the insights from experts like you all.
Thanks a lot in advance
Thanks a lot in advance
Code:
Private Sub ServiceDetailsUserForm_Initialize()
MachineCodeComboBox.Value = ""
ServiceDateTextBox.Value = "DD/MM/YYYY"
ServiceDateTextBox.ForeColor = &HC0C0C0 '<~~ Grey Color
ServiceProviderComboBox.Value = ""
TechnicalPersonNameTextBox.Value = ""
PONumberTextBox.Value = ""
CUSDECNumberTextBox.Value = ""
SupervisorTextBox.Value = ""
InhouseMaterialComboBox.Value = ""
MaterialUOMTextBox.Value = ""
MaterialQuantityTextBox.Value = ""
PurchasedMaterialComboBox.Value = ""
UOMComboBox.Value = ""
QuantityTextBox.Value = ""
ServiceProviderMaterialComboBox.Value = ""
ServiceProviderUOMComboBox.Value = ""
ServiceProviderQuantityTextBox.Value = ""
ExtraMaterialComboBox.Value = ""
ExtraMaterialUOMComboBox.Value = ""
ExtraMaterialQuantityTextBox.Value = ""
LastServiceDateTextBox.Value = "DD/MM/YYYY"
LastServiceDateTextBox.ForeColor = &HC0C0C0 '<~~ Grey Color
NextServiceDateTextBox.Value = "DD/MM/YYYY"
NextServiceDateTextBox.ForeColor = &HC0C0C0 '<~~ Grey Color
MachineCodeComboBox.SetFocus
End Sub
Private Sub ClearCommandButton_Click()
Call ServiceDetailsUserForm_Initialize
End Sub
Private Sub InhouseMaterialComboBox_Change()
With Me
.MaterialUOMTextBox = Application.WorksheetFunction.VLookup(Me.InhouseMaterialComboBox, Sheet6.Range("B:C"), 2, False)
End With
End Sub
Private Sub ServiceDateTextBox_AfterUpdate()
With ServiceDateTextBox
If .Text = "" Then
.ForeColor = &HC0C0C0
.Text = "DD/MM/YYYY"
End If
End With
End Sub
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
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, 14).Value = ExtraMaterialComboBox.Value
Cells(emptyRow, 15).Value = ExtraMaterialUOMComboBox.Value
Cells(emptyRow, 16).Value = ExtraMaterialQuantityTextBox.Value
Cells(emptyRow, 2).Value = LastServiceDateTextBox.Value
Cells(emptyRow, 2).Value = NextServiceDateTextBox.Value
End If
End Sub
Private Sub UserForm_Initialize()
ServiceDateTextBox.ForeColor = &HC0C0C0 '<~~ Grey Color
ServiceDateTextBox.Text = "DD/MM/YYYY"
LastServiceDateTextBox.ForeColor = &HC0C0C0 '<~~ Grey Color
LastServiceDateTextBox.Text = "DD/MM/YYYY"
NextServiceDateTextBox.ForeColor = &HC0C0C0 '<~~ Grey Color
NextServiceDateTextBox.Text = "DD/MM/YYYY"
Dim m As Long
For m = 2 To Sheet1.Range("C1000000").End(xlUp).Offset(3, 0).Row
Me.MachineCodeComboBox.AddItem Sheet1.Cells(m, 3).Value
Next m
Dim i As Long
For i = 2 To Sheet3.Range("B1000000").End(xlUp).Offset(2, 0).Row
Me.ServiceProviderComboBox.AddItem Sheet3.Cells(i, 2).Value
Next i
Dim n As Long
For n = 2 To Sheet6.Range("B1000000").End(xlUp).Offset(2, 0).Row
Me.InhouseMaterialComboBox.AddItem Sheet6.Cells(n, 2).Value
Next n
PurchasedMaterialComboBox.AddItem "Battery Water"
PurchasedMaterialComboBox.AddItem "Lubricant oil"
ServiceProviderMaterialComboBox.AddItem "Battery Water"
ServiceProviderMaterialComboBox.AddItem "Lubricant oil"
ExtraMaterialComboBox.AddItem "Battery Water"
ExtraMaterialComboBox.AddItem "Lubricant oil"
UOMComboBox.AddItem "Litre"
UOMComboBox.AddItem "Meter"
UOMComboBox.AddItem "Each"
UOMComboBox.AddItem "Kilogram"
ServiceProviderUOMComboBox.AddItem "Litre"
ServiceProviderUOMComboBox.AddItem "Meter"
ServiceProviderUOMComboBox.AddItem "Each"
ServiceProviderUOMComboBox.AddItem "Kilogram"
ExtraMaterialUOMComboBox.AddItem "Litre"
ExtraMaterialUOMComboBox.AddItem "Meter"
ExtraMaterialUOMComboBox.AddItem "Each"
ExtraMaterialUOMComboBox.AddItem "Kilogram"
End Sub
Private Sub InhouseMaterialComboBox_Change()
With Me
.MaterialUOMTextBox = Application.WorksheetFunction.VLookup(Me.InhouseMaterialComboBox, Sheet6.Range("B:C"), 2, False)
End With
End Sub
Last edited by a moderator: