I am using a textboxes in a userform for ordering supplies. I want to check
to see if the quantity entered exceeds the max stock level. The userform is
executed while from Worksheet("Order"). The value for the max stock level
(for this particular medication, is on Worksheet("List") in cell Z3.

I had a macro that I thought would execute AFTERUPDATE. Apparently I am not
doing something right. Not sure if this matters, but the computers that will
be using this have touchscreens and the computer I am writing it on does not.

The user will first enter the ambulance number, then tab, enter, or touch
screen, to advance to the next textbox. Lets say the user enters a value of
5 for Textbox6. The macro should execute, afterupdate, to pull the max stock
level (4) and compare. If 54, then I will clear Textbox6 and setfocus to
textbox6 after displaying a msgbox about the invalid entry.

Any ideas on why it may not be executing, or show me what it should look
like. I am on a different computer to send this message then what my code is
on so I cannot copy and paste.

Thanks,
Les

It would help to see what code you are using now to validate the data - or is
it that you have no code associated with it at all?

If you don't have any code at all associated with it, then this should help:

Private Sub TextBox6_Change()
Dim enteredText As String
Dim maxOrder As Variant

enteredText = Me!TextBox6.Text
maxOrder = ThisWorkbook.Worksheets("List").Range("Z3")
If Val(enteredText) maxOrder Then
MsgBox "You have attempted to over-order. Please try again.", _
vbOKOnly + vbCritical, "Max Order Qty is: " & maxOrder
Me!TextBox6 = ""
Me!TextBox6.SetFocus
Exit Sub
End If
End Sub

Just go to the UserForm in VB Editor and double-click on TextBox 6 and the
code for the _Change() event show appear as a stub with just the first & last
lines of it. Copy the code between the Sub and End Sub statements above and
paste them into the stub in the VBE. Test it out. It should actually test
while typing an entry - so if you typed 5, it would immediately alert, and if
you typed 10, it would alert as soon as you typed the zero.

"WLMPilot" wrote:

I am using a textboxes in a userform for ordering supplies. I want to check
to see if the quantity entered exceeds the max stock level. The userform is
executed while from Worksheet("Order"). The value for the max stock level
(for this particular medication, is on Worksheet("List") in cell Z3.

I had a macro that I thought would execute AFTERUPDATE. Apparently I am not
doing something right. Not sure if this matters, but the computers that will
be using this have touchscreens and the computer I am writing it on does not.

The user will first enter the ambulance number, then tab, enter, or touch
screen, to advance to the next textbox. Lets say the user enters a value of
5 for Textbox6. The macro should execute, afterupdate, to pull the max stock
level (4) and compare. If 54, then I will clear Textbox6 and setfocus to
textbox6 after displaying a msgbox about the invalid entry.

Any ideas on why it may not be executing, or show me what it should look
like. I am on a different computer to send this message then what my code is
on so I cannot copy and paste.

Thanks,
Les

I have code associated with it. However, the first line I have is:
Private Sub TextBox6_Afterupdate()

What is the difference between Change() and AfterUpdate()?

Thanks for your help. I will switch to Change() and see happens.

Les


"JLatham" wrote:

It would help to see what code you are using now to validate the data - or is
it that you have no code associated with it at all?

If you don't have any code at all associated with it, then this should help:

Private Sub TextBox6_Change()
Dim enteredText As String
Dim maxOrder As Variant

enteredText = Me!TextBox6.Text
maxOrder = ThisWorkbook.Worksheets("List").Range("Z3")
If Val(enteredText) maxOrder Then
MsgBox "You have attempted to over-order. Please try again.", _
vbOKOnly + vbCritical, "Max Order Qty is: " & maxOrder
Me!TextBox6 = ""
Me!TextBox6.SetFocus
Exit Sub
End If
End Sub

Just go to the UserForm in VB Editor and double-click on TextBox 6 and the
code for the _Change() event show appear as a stub with just the first & last
lines of it. Copy the code between the Sub and End Sub statements above and
paste them into the stub in the VBE. Test it out. It should actually test
while typing an entry - so if you typed 5, it would immediately alert, and if
you typed 10, it would alert as soon as you typed the zero.

"WLMPilot" wrote:

I am using a textboxes in a userform for ordering supplies. I want to check
to see if the quantity entered exceeds the max stock level. The userform is
executed while from Worksheet("Order"). The value for the max stock level
(for this particular medication, is on Worksheet("List") in cell Z3.

I had a macro that I thought would execute AFTERUPDATE. Apparently I am not
doing something right. Not sure if this matters, but the computers that will
be using this have touchscreens and the computer I am writing it on does not.

The user will first enter the ambulance number, then tab, enter, or touch
screen, to advance to the next textbox. Lets say the user enters a value of
5 for Textbox6. The macro should execute, afterupdate, to pull the max stock
level (4) and compare. If 54, then I will clear Textbox6 and setfocus to
textbox6 after displaying a msgbox about the invalid entry.

Any ideas on why it may not be executing, or show me what it should look
like. I am on a different computer to send this message then what my code is
on so I cannot copy and paste.

Thanks,
Les