Userform - Textbox only accept number-CodePudding

I've been searching for a solution to change apperance of my textbox into red when the entered value is not a number. Actually, I succeeded with a textbox only but I have 14 textboxes at total. I've tried with the codes below:

Private Sub Justi_num()

Dim i As Integer

For i = 2 To 14
    If IsNumeric(Controls("TextBox" & i).Value) Then
        Controls("TextBox" & i).BackColor = RGB(255, 255, 255) 'Blanc
    Else 'Sinon
         Controls("TextBox" & i).BackColor = RGB(247, 205, 201) 'Rouge clair
    End If
Next i

End Sub

But it didnt work. Any help? Thanks in advance.

CodePudding user response：

You first need to ensure that your code is called whenever the user enters something. This is done with Event-Routines. Controls (like buttons, checkboxes, textboxes and so on) have different events like change (something was changed), dblClick (there was a double click on the control) and so on.

If you want some code to be executed, you need to put code into that event routine. The name of the event routine is combined by the name of the control and the event name. If the name is not following that rule, the code is not called automatically.

In design mode, go to the form editor and double click onto one of your textboxes: The editor will open the text editor window and create a subroutine with the most common event of that control (for a textbox, that's the Change-event, for a button, it would be the Click-event):

Private Sub TextBox1_Change()

End Sub

You could call your routine Justi_num from this routine:

Private Sub TextBox1_Change()
    Justi_num
End Sub

However, you will need to create such an event routine for every of your text boxes, there is no way around that (ok, yes, there is, but it is much too complicated for a beginner).

Private Sub TextBox2_Change()
    Justi_num
End Sub

Private Sub TextBox3_Change()
    Justi_num
End Sub

and so on...

One thing: Your routine Justi_num handles all textboxes, no matter which textbox was changed. You could change the code so that the event routine passes the information which control was changed and the color changing routine handles only that control:

Private Sub TextBox1_Change()
    setTextBoxColor Me.TextBox1
End Sub

Private Sub TextBox2_Change()
    setTextBoxColor Me.TextBox2
End Sub

(and so on...)

Private Sub setTextBoxColor(tbox As Control)
    If IsNumeric(tbox.Value) Or tbox.Value = "" Then
        tbox.BackColor = vbWhite
    Else
        tbox.BackColor = RGB(247, 205, 201)
    End If
End Sub

CodePudding user response：

I'll try to help:

One thing that may fail is that "IsNumeric" gives a boolean result. Try using If IsNumeric(Controls("TextBox" & i).Value) = True Then

It would be something like:

Private Sub Justi_num()

Dim i As Integer
Dim NA As Boolean
NA = False

For i = 2 To 14
    NA = IsNumeric(Controls("TextBox" & i).Value)
    If NA = True Then
        Controls("TextBox" & i).BackColor = RGB(255, 255, 255) 'Blanc
    Else 'Sinon
         Controls("TextBox" & i).BackColor = RGB(247, 205, 201) 'Rouge clair
    End If
Next i

End Sub

Hope it helps!