CODEWITHSUNDEEP
vbaexceluserform
FredGooch
FredGooch

Reputation: 191

How to clear userform textbox without calling the _Change function?

I have a userform in Excel with textboxes meant for numeric data only. I want to clear the textbox when it detects bad entry and gives an error message, but I don't want to have the textbox's _Change function called again or else the message pops up twice because I change the text to "". I didn't see a built in clear function.. is there a better way to do this?

Private Sub txtbox1_Change()
    txt = userform.txtbox1.Value
    If Not IsNumeric(txt) Then
        disp = MsgBox("Please only enter numeric values.", vbOKCancel, "Entry Error")
        txtbox1.Text = ""
    End If
End Sub

Upvotes: 1

Views: 9115

Answers (7)

A.S.H
A.S.H

Reputation: 29332

You can add this line at the very beginning 

sub txtbox1_Change()
    If txtbox1.Text = "" Or txtbox1.Text = "-" Then Exit Sub  '<~~~

Alternatively, I found this even shorter and interesting:

Private Sub txtbox1_Change()
    If Not IsNumeric(txtbox1.Text & "0") Then
       disp = MsgBox("Please only enter numeric values.", vbOKCancel, "Entry Error")
       txtbox1.Text = ""
    End If
End Sub

The interesting part is that it accepts to enter things like ".2", "-3.2", and also "5e3", the last case being not allowed by the other methods!

Turning it into a while loop can remove only the last bad typed character(s):

Private Sub txtbox1_Change()
    t = txtbox1.Text
    Do While t <> "" And Not IsNumeric(t) And Not IsNumeric(t & "0")
       t = Mid(t, 1, Len(t) - 1)
    Loop
    txtbox1.Text = t
End Sub

Upvotes: 1

DFPercush
DFPercush

Reputation: 1

You can't stop the _Changed event from firing. I would advise you to back up a couple of steps in your design and ask if you can get the job done without having to clear it in the first place. In FoxPro we would set the 'format' to 9999.99 and it would automatically prevent users from typing alpha characters, but I think that particular field was unique to FP. You can hook the _Changed event and perform your own validation there. I would suggest not filtering individual key strokes, but validating the whole value each time it's changed. 

If Text1.Value <> str(val(Text1.Value)) Then
    Text1.Value = previousValue
EndIf

... which will require keeping a backup variable for the previous value, but I'm sure you can figure that out. There may be certain edge cases where VB's string-number conversion functions don't exactly match, like exponential notation as you mentioned, so you may need a more sophisticated check than that. Anyway, this will make it impossible to even enter a bad value. It also provides a better user experience because the feedback is more immediate and intuitive. You may notice that the value is being changed inside the _Changed event, which should raise a knee jerk red flag in your mind about infinite loops. If you do this, make sure that your previous value has already been validated, keeping in mind that the initial value will be an empty string. As such, the recursive call will skip over the If block thus terminating the loop. In any case, what you would consider "better" may differ depending on who you ask, but hopefully I've given you some food for thought.

Upvotes: 0

seadoggie01
seadoggie01

Reputation: 530

Declare a global boolean and at the beginning of each sub, add an if statement which exits the sub if the boolean is true. When you get an error message, set the value to true, and nothing will happen. Then set it to false again.

Dim ufEventsDisabled As Boolean

Private Sub txtbox1_Change()
  'repeat the following line everywhere that you don't want to update
  if ufeventsdisabled then exit sub
  txt = userform.txtbox1.Value
  If Not IsNumeric(txt) Then
    disp = MsgBox("Please only enter numeric values.", vbOKCancel, "Entry Error")
    ufeventsdisabled = true
    txtbox1.Text = ""
    ufeventsdisabled = false
  End If
End Sub

*Credit goes to mikerickson from mrexcel.com

Upvotes: 0

Estevam Garcia
Estevam Garcia

Reputation: 445

You can do this way, as shown here

Private Sub TextBox1_Change()
    OnlyNumbers
End Sub

Private Sub OnlyNumbers()

    If TypeName(Me.ActiveControl) = "TextBox" Then

        With Me.ActiveControl

            If Not IsNumeric(.Value) And .Value <> vbNullString Then

                MsgBox "Sorry, only numbers allowed"

                .Value = vbNullString

            End If
        End With
    End If
End Sub

Upvotes: 1

FredGooch
FredGooch

Reputation: 191

Seems since there is nothing built in that can do what I want, this would be the simplest way to handle the problem:

Private Sub txtbox1_Change()
txt = userform.txtbox1.Value
If (Not IsNumeric(txt)) And (txt <> "") Then
    disp = MsgBox("Please only enter numeric values.", vbOKCancel, "Entry Error")
    txtbox1.Text = ""
End If
End Sub

Upvotes: 0

Christian
Christian

Reputation: 536

A simple way to achieve this is to use the _Exit() Function:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Not IsNumeric(TextBox1.Value) Then
        MsgBox "Please only enter numeric values.", vbCritical, "Error"
End If
End Sub

This triggers as soon as the text box looses Focus.

Upvotes: 3

Hugues Paquet Blanchette
Hugues Paquet Blanchette

Reputation: 612

prevent user from typing Alpha chars:

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
    Case Asc("0") To Asc("9")
    Case Asc("-")
        If Instr(1,Me.TextBox1.Text,"-") > 0 Or Me.TextBox1.SelStart > 0 Then
            KeyAscii = 0
        End If
    Case Asc(".")
        If InStr(1, Me.TextBox1.Text, ".") > 0 Then
            KeyAscii = 0
        End If
    Case Else
        KeyAscii = 0
End Select
End Sub

Hope this helps! -Hugues

Upvotes: 1

Related Questions