VBA validation rule

[emily2309]

I am trying to create a validation rule for a text box on my user input form. The input form is basically used to add food items to a spreadsheet. The user must enter an amount. I want the amount to be between 1 and 30000, so that if the user adds something outside of this (i.e text or -1), an error message will appear. Please help, i'm not very advanced with VBA

 

[Jon von der Heyden]

Hi Emily

Try;

[COLOR="Blue"]Private[/COLOR] [COLOR="Blue"]Sub[/COLOR] TextBox1_Exit([COLOR="Blue"]ByVal[/COLOR] Cancel [COLOR="Blue"]As[/COLOR] MSForms.ReturnBoolean)
    [COLOR="Blue"]With[/COLOR] Me.TextBox1
        [COLOR="Blue"]If[/COLOR] .Value < 1 [COLOR="Blue"]Or[/COLOR] .Value > 30000 [COLOR="Blue"]Then[/COLOR]
            [COLOR="Blue"]MsgBox[/COLOR] prompt:="Must be a # between 1 and 30000!", Buttons:=vbCritical, Title:="Invalid Entry"
            Cancel = [COLOR="Blue"]True[/COLOR]
        [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[emily2309]

Sorry to be a pain, the box i'm trying to set it to is called TxtAmount, where do i need to add the name?

 

[Jon von der Heyden]

Anywhere it says TextBox1, replace with TxtAmount.

Private Sub [COLOR=red]TxtAmount[/COLOR]_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    With Me.[COLOR=red]TxtAmount[/COLOR]
        If .Value < 1 Or .Value > 30000 Then
            MsgBox prompt:="Must be a # between 1 and 30000!", Buttons:=vbCritical, Title:="Invalid Entry"
            Cancel = True
       End If
    End With
End Sub

 

[emily2309]

When the validation box appears, i want the box (TxtAmount) to be blank when i return to the form. Any ideas?

 

[Jon von der Heyden]

Private Sub [COLOR=black]TxtAmount[/COLOR]_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    With Me.[COLOR=black]TxtAmount[/COLOR]
        If .Value < 1 Or .Value > 30000 Then
            [COLOR=red].Value=""[/COLOR]
            MsgBox prompt:="Must be a # between 1 and 30000!", Buttons:=vbCritical, Title:="Invalid Entry"
            Cancel = True
       End If
    End With
End Sub

 

[emily2309]

Thanks :D