Validate the value entered to a text box of a User Form using the data in another Work sheet

K

KTSARA

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

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
Hi,
try using the Change Event for the textbox users enter qty in to

something like following
VBA Code: 
Private Sub MaterialQuantityTextBox_Change()
    Dim QtyAvailable As Long
    QtyAvailable = Val(Me.MaterialUOMTextBox.Value)
    With MaterialQuantityTextBox
    If Val(.Value) > QtyAvailable Or QtyAvailable < 1 Then
        MsgBox "Qty Available: " & QtyAvailable & Chr(10) & _
            "Not Enough Materials Available.", 48, "Entry Error"
        .SetFocus
    End If
    End With
End Sub

Adjust code as required


BTW - I noticed in the SubmitCommandButton_Click code you are posting three control values to the same range

Code: 
Cells(emptyRow, 2).Value = ServiceDateTextBox.Value
Cells(emptyRow, 2).Value = LastServiceDateTextBox.Value
Cells(emptyRow, 2).Value = NextServiceDateTextBox.Value


Hope Helpful

Dave
 
Upvote 0
Hello Dave, Thank you very much for trying to help me. I think my code was a little unclear. I want to validate the data I am going to enter in to the Service Details data sheet using the data already in the Inhouse Inventory data sheet.
This is what I have done and this code generates an error message for whatever value I enter to the text box. If you could help me to find what I have done wrong, it would be much appreciated.
Thanks

Private Sub MaterialQuantityTextBox_AfterUpdate()

If MaterialQuantityTextBox.Value > Application.WorksheetFunction.VLookup(Me.InhouseMaterialComboBox, Sheet6.Range("B:D"), 3, False) Then
MsgBox " Quantity is greater than the quantity available in the Inventory. Enter a valid quantity"
End If

End Sub
 
Upvote 0
KTSARA said:
Hello Dave, Thank you very much for trying to help me. I think my code was a little unclear. I want to validate the data I am going to enter in to the Service Details data sheet using the data already in the Inhouse Inventory data sheet.
This is what I have done and this code generates an error message for whatever value I enter to the text box. If you could help me to find what I have done wrong, it would be much appreciated.
Thanks

VBA Code: 
Private Sub MaterialQuantityTextBox_AfterUpdate()
    If MaterialQuantityTextBox.Value > Application.WorksheetFunction.VLookup(Me.InhouseMaterialComboBox, Sheet6.Range("B:D"), 3, False) Then
        MsgBox " Quantity is greater than the quantity available in the Inventory. Enter a valid quantity"
    End If
End Sub
Click to expand...
 
Upvote 0
Hi,
No worries, as with most things, always a bit of of a guess when offering help

Here is another, Try using a type conversion function to coerce your TextBox value to correct data type & see if this resolves

VBA Code: 
Private Sub MaterialQuantityTextBox_AfterUpdate()

If Val(MaterialQuantityTextBox.Value) > Application.WorksheetFunction.VLookup(Me.InhouseMaterialComboBox, Sheet6.Range("B:D"), 3, False) Then
MsgBox " Quantity is greater than the quantity available in the Inventory. Enter a valid quantity"
End If

End Sub

If still have issues & if possible, place copy of your workbook with sample data in a dropbox & post a link to it here. This will help myself & others here to resolve your issue.


Dave
 
Upvote 0
KTSARA said:
Hello Dave,
Your code works perfectly. Thank you so much for taking your time to help me.:)
Click to expand...

Glad suggestion resolved issue for you - many thanks for feedback it is very much appreciated

Dave
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Numeric Value returned when Time format returned to User Form TextBox
Replies
3
Views
78
6StringJazzer
6StringJazzer
A
  • Question Question
Cannot enter text in textbox on form
Replies
3
Views
273
Anthony47
A
P
  • Solved
Excel VBA, Vlookup to another sheet in same work book
Replies
6
Views
1K
StephenCrump
S
D
  • Question Question
VBA code works when called directly, but not from another routhine
Replies
7
Views
951
davidepstein22
D
M
  • Question Question
I need help with this code to modify the data into a table using Userform for VBA Excel (I want to do the range unlimited instead to do C7:C80000
Replies
5
Views
651
Dan_W
D

Forum statistics

Threads
1,223,885
Messages
6,175,183
Members
452,615
Latest member
bogeys2birdies

Share this page

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