Disable _Change() upon initialization of userform

birdieman · Jan 31, 2016

I have several textboxes in a userform with _change() events attached and the first thing that happens in the sub is a function that makes sure the user inputs a real number (as opposed to a letter) without any $ attached. When the userform is initialized, it is loaded with the saved data, which is formatted like $5,000, which trips the number-checking function, which gives me an message "please key in a number", when there is already a number in there.

So, my question is: Is it possible to temporarily deactivate the _change event ONLY upon initialization? Then after initialization, turn it on again?

Andrew Poulsom · Jan 31, 2016

Read the text in the link I posted carefully then apply it.

mikerickson · Jan 31, 2016

birdieman said:
1. If the initialization has occurred by the time the program gets to the last line, which turns the events back on, why do I need to check each sub after that? All I want is for the initialization to occur without tripping the _change events. After that, I want the _change events to trigger as normal.

2. If the above code (as written) does what I describe in questions #1, when do i turn it back on?

The EnableEvents does not "turn off events"
EnableEvents is a boolean variable. The events fire whether it is True or False.
But, by putting the test at the start of each of your event routines, they won't run.

You cannot prevent the userforms' Change events from firing. But, by using the EnableEvents variable, you can control whether those events do something (when EnableEvents is True) or do nothing (when EnableEvents is False).

birdieman · Jan 31, 2016

thank you for the explanation, now I understand. I also found out, to my chagrin, that if Sub 1 has both a _change event and an _exit event, that the _change event will fire as data is entered (as it should), but it will FIRE AGAIN once the "enter" key is hit to exit the textbox. So, my problem is that as data is entered in a _change sub, each number is checked in a number-checking function, which works fine. However, in the _exit sub there is a format line, which puts the data into currency ($5000), and when I press "enter" to move on to next textbox, the _change fires again, which now rejects my newly formatted number because it does not recognize the $ or any commas. Any way to have it NOT re-fire the _change sub when exiting? Or, is there just something else wrong. the code

Code:

Private Sub TextBox5_Change()
If EnableEvents = False Then Exit Sub

If Not IsDigitsOnly(TextBox5) Then
 MsgBox ("TextBox 5 Please enter a number without blanks or any special characters like $,%, etc,"), vbCritical
 Cancel = True
 TextBox5 = ""
 End If
  End Sub
 
 Private Sub TextBox5_exit(ByVal Cancel As MSForms.ReturnBoolean)
 TextBox5 = Format(TextBox5, "$###,###,##0")
 Sheet1.Range("a5").Value = Format(TextBox5.Value, "$###,###,##0")
 End Sub

Function IsDigitsOnly(Value As String) As Boolean
    IsDigitsOnly = Len(Value) > 0 And Not Value Like "*[!0-9]*"
    End Function

Andrew Poulsom · Jan 31, 2016

Additionally, the default for a Boolean variable is False. So, if your event procedures test for False, it must be explicitly set to True for them to continue.

Andrew Poulsom · Jan 31, 2016

Disable events in your Exit event procedure when you change the TextBox's Value. Enable events after that's done.

mikerickson · Jan 31, 2016

birdieman said:
thank you for the explanation, now I understand. I also found out, to my chagrin, that if Sub 1 has both a _change event and an _exit event, that the _change event will fire as data is entered (as it should), but it will FIRE AGAIN once the "enter" key is hit to exit the text box...

Then use the EnableEvents variable again in the Exit Event.
(BTW, it looks like the Cancel = True line in the Change event is doing nothing. Are you using Option Explicit?)

Code:

Private Sub TextBox5_Change()
    If EnableEvents = False Then Exit Sub

    If Not IsDigitsOnly(TextBox5) Then
        MsgBox ("TextBox 5 Please enter a number without blanks or any special characters like $,%, etc,"), vbCritical
       Cancel = True
       TextBox5 = ""
     End If
End Sub
 
Private Sub TextBox5_exit(ByVal Cancel As MSForms.ReturnBoolean)
    If EnableEvents = False Then Exit Sub: Rem This line should be in every event routine in the user form.

    [COLOR="#FF0000"]EnableEvents = False[/COLOR]
    TextBox5 = Format(TextBox5, "$###,###,##0")
    [COLOR="#FF0000"]EnableEvents = True[/COLOR]
    Sheet1.Range("a5").Value = Format(TextBox5.Value, "$###,###,##0")
End Sub

birdieman · Jan 31, 2016

not that I know of. Do you mean to put "Option Explicit" at the very very top of my userform code? Like above "Dim EnableEvents AS Boolean"?

thanks for the tip above, you guys have taught me a lot -- even though it does not seem like it

mikerickson · Jan 31, 2016

Yes, I mean putting Option Explict as the first line of every module of your project.
If you go to the VBEditor Preferences >> Editor Tab and check the Require Variable Declaration box, the VBEditor will do that automatically.
It will save you many headaches by notifying you of minor typos. (Plus declaring your variables will save memory).

Since you seem to have a handle on your question, let me bring up something that I noticed in post #13.
Using the Change event for validation is very volatile. Per post#13, if the user accidentally presses a letter key, their entire previous entry is deleted and they have to start all over again entering the number in the TextBox.

While there are various fixes for that, one style that I like is to use the KeyPress event to prevent the user from making a faulty entry. Try replacing the code from post #13 with this and see if the user friendliness is to your liking.

Code:

Private Sub TextBox5_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    With TextBox5
        If (0 < Len(.Text)) And (.SelStart = 0) And (.Text Like "$*") Then
            Rem if the first character is $ and the user is trying to change it
            
            .SelStart = 1: .SelLength = Len(.Text) - 1  ' change the selection to exlude first character
            
            KeyAscii = 0  '  ignore the user's key press
            Beep  ' can be replaced with MsgBox if desired

        ElseIf Chr(KeyAscii) Like "[!0-9]" Then
            Rem if the user pressed a non-numeric key
            
            KeyAscii = 0  '  ignore the user's key press 
            Beep ' can be replaced with MsgBox if desired
        End If
    End With
End Sub

Private Sub TextBox5_AfterUpdate()
    If Not (EnableEvents) Then Exit Sub
    With TextBox5
        .Text = Format(Val(Replace(Replace(.Text, "$", vbNullString), ",", vbNullString)), "$#,##0")
    End With
End Sub

I'm also not a big fan of putting formatting characters inside the TextBox. A static Label with a $, just to the left of the Textbox makes the coding much easier.

birdieman · Jan 31, 2016

Thanks for the suggestions. Yes, I learned the hard way about formatting the numbers inside the textbox, although I think it looks much better than having a $ or % as a label -- just not quite as clean with labels, in my opinion. Regarding the method of data validation, if what you mean as "volatile" is a little inconvenience for the user - I can live with that on this beginning project -- the numbers are not that long to re-key. Plus, I am almost finished and do not want to re-do stuff because I will probably screw it or something else up. If by "volatile" you mean my way will eventually not work/mess up/develop problems, I will re-think about it.

I programmed FORTRAN 35-40 years ago in college (haven't since), but VB is different and seems much harder for me to grasp -- does not seem as sequential as FORTRAN. GOTO is easy for me to understand. Anyway, thanks for the help -- I am sure I am not done asking question on the forum. My next subject is to learn ON ERROR, so my whole program will not crash when a user does something that I have not anticipated. thanks Again

mikerickson · Jan 31, 2016

Ah yes....Fortran 4 with batch processing of cards. (Dutifully delivered to the basement of Hutchison Hall)

By volatile, I mean "inconvenient".

Disable _Change() upon initialization of userform

birdieman

Well-known Member

Andrew Poulsom

MrExcel MVP

Excel Facts

mikerickson

MrExcel MVP

birdieman

Well-known Member

Andrew Poulsom

MrExcel MVP

Andrew Poulsom

MrExcel MVP

mikerickson

MrExcel MVP

birdieman

Well-known Member

mikerickson

MrExcel MVP

birdieman

Well-known Member

mikerickson

MrExcel MVP

Similar threads

Forum statistics

Share this page

Disable _Change() upon initialization of userform

Well-known Member

MrExcel MVP

Excel Facts

MrExcel MVP

Well-known Member

MrExcel MVP

MrExcel MVP

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock