CODEWITHSUNDEEP
excelvbauserform
westinq
westinq

Reputation: 43

How to validate several userform textboxes?

I have a workbook with userforms to write to several numeric and date fields. I need to validate the textbox control for proper numbers and dates.

Rather than replicate the validation for each textbox, I thought I would call a common subprocedure within the BeforeUpdae event of each textbox.

I have two problems.

  1. If I execute the form and test using text in tbAmount box, it seems the ContolValidate procedure is not called.
    If I run it in break mode with a breakpoint on Call ContolValidate(What, CurrentControl), it will step through that procedure.

  2. Even though it steps through the procedure, the Cancel = True does not seem to work.
    If I paste the ContolValidate code directly in the BeforeUpdate, the Cancel = True does work.

This code is all on the userform.

Private Sub tbAmount1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Dim What As String
    Dim CurrentControl As Control

    What = "NumericField"
    Set CurrentControl = Me.ActiveControl
    Call ContolValidate(What, CurrentControl)
End Sub

Private Sub ContolValidate(What, CurrentControl)
    If Not IsNumeric(CurrentControl.Value) Then
        ErrorLabel.Caption = "Please correct this entry to be numeric."
        Cancel = True
        CurrentControl.BackColor = rgbPink
        CurrentControl.SelStart = 0
        CurrentControl.SelLength = Len(CurrentControl.Value)
    Else
        If CurrentControl.Value < 0 Then
            ErrorLabel.Caption = "This number cannot be negative."
            Cancel = True
            CurrentControl.BackColor = rgbPink
            CurrentControl.SelStart = 0
            CurrentControl.SelLength = Len(CurrentControl.Value)
        End If
    End If
End Sub

Private Sub tbAmount1_AfterUpdate()
    ErrorLabel.Visible = False
    tbAmount1.BackColor = Me.BackColor
End Sub

Upvotes: 0

Views: 173

Answers (1)

FunThomas
FunThomas

Reputation: 29296

(1) When your control is named tbAmount1 and the code is in the code-behind module of the form, the trigger should fire.

(2) As @shahkalpesh mentioned in his comment, Cancel is not known in your validate-routine. Putting Option Explicit at the top of you code would show you that.
I would suggest to convert the routine to a function. In the code below, I return True if the content is okay and False if not (so you need to put a Not to the result to set the Cancel-parameter)

Private Sub tbAmount1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Cancel = Not ControlValidate("NumericField", Me.ActiveControl)
End Sub

Private Function ControlValidate(What, CurrentControl) As Boolean
    ControlValidate = False

    If Not IsNumeric(CurrentControl.Value) Then
        errorlabel.Caption = "Please correct this entry to be numeric."
    ElseIf CurrentControl.Value < 0 Then
        errorlabel.Caption = "This number cannot be negative."
    Else
        ControlValidate = True   ' Input is okay.
    End If
    
    If ControlValidate Then
        CurrentControl.BackColor = vbWhite
    Else
        CurrentControl.BackColor = rgbPink
        CurrentControl.SelStart = 0
        CurrentControl.SelLength = Len(CurrentControl.Value)
    End If
End Function

P.S.: I changed the name to ControlValidate - "contol" seems wrong to me...

Upvotes: 1

Related Questions