Disable _Change() upon initialization of userform

[birdieman]

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]

Here's a method I use:

Suppressing Events In UserForms

 

[birdieman]

Does "tabbing" or hitting "enter" or using the cursor to move around/through a pre-populated userform -- without changing anything in any textbox -- trigger a _change() event?

 

[Andrew Poulsom]

The Change event fires when the value in the TextBox is changed.

 

[birdieman]

I read the link on disabling events. Do i have this correct?

In my userform_Initialize, do I put:

enableevents=false for the first line of the sub
'do all of the userform initializing stuff here
enableevents=true for the last line of the initialization sub

and that is it?

Or, do I do the above AND put something like "If enable events =false, then exit" in each _change() sub at the beginning of the sub?

thanks for your help

 

[birdieman]

Sorry to keep bothering you. I want to turn off the _change() events before initializing, then initialize, then turn back on. this is the only time I want to turn them off and on. Here is the code i have, but it gives me an "object required" error.

Private Sub userform_Initialize()
UserForm.EnableEvents = False

TextBox1.Value = Format(Val(CStr(Sheet1.Range("a1").Value)), "###,###,##0")
TextBox2.Value = Format(Val(CStr(Sheet1.Range("a2").Value)), "0.0%")
TextBox3.Value = Format(Val(CStr(Sheet1.Range("a3").Value)), "0.0%")
TextBox4.Value = Format(Val(CStr(Sheet1.Range("a4").Value)), "0.0%")
TextBox5.Value = Format(Val(CStr(Sheet1.Range("a5").Value)), "###,###,##0")
UserForm.EnableEvents = True
End Sub

 

[mikerickson]

...

Or, do I do the above AND put something like "If enable events =false, then exit" in each _change() sub at the beginning of the sub?

thanks for your help

Andrew is offline, but yes you need

If enable events =false, then exit sub

to be the start of the change event for every control that you write.

 

[birdieman]

I want to deactivate the _change() events, initialize the userform, then reactivate/turn on enableevents. I have the following code:

Private Sub userform_Initialize()
UserForm.EnableEvents = False

TextBox1.Value = Format(Val(CStr(Sheet1.Range("a1").Value)), "###,###,##0")
TextBox2.Value = Format(Val(CStr(Sheet1.Range("a2").Value)), "0.0%")
TextBox3.Value = Format(Val(CStr(Sheet1.Range("a3").Value)), "0.0%")
TextBox4.Value = Format(Val(CStr(Sheet1.Range("a4").Value)), "0.0%")
TextBox5.Value = Format(Val(CStr(Sheet1.Range("a5").Value)), "###,###,##0")
UserForm.EnableEvents=True
End Sub

But I get an "object required" error.

 

[mikerickson]

Try this. The Dim EnableEvents line, placed before any procedure in the module, declares EnableEvents a private variable of the user form.
Note that the line

EnableEvents = True

must be the last line of the Intialize event, otherwise EnableEvents will be false and the event code will be blocked.

Dim EnableEvents As Boolean

Private Sub Userform_Initialize()
    EnableEvents = False
    TextBox1.Value = Format(Val(CStr(Sheet1.Range("a1").Value)), "###,###,##0")
    TextBox2.Value = Format(Val(CStr(Sheet1.Range("a2").Value)), "0.0%")
    TextBox3.Value = Format(Val(CStr(Sheet1.Range("a3").Value)), "0.0%")
    TextBox4.Value = Format(Val(CStr(Sheet1.Range("a4").Value)), "0.0%")
    TextBox5.Value = Format(Val(CStr(Sheet1.Range("a5").Value)), "###,###,##0")

    EnableEvents=True: Rem This line must be the last line in the Initialize event
End Sub

Private Sub TextBox1_Change()
    If EnableEvents = False Then Exit Sub
    ' code
End Sub

Private Sub TextBox2_Change()
    If EnableEvents = False Then Exit Sub
    ' code
End Sub

' etc

Private Sub TextBox5_Change()
    If EnableEvents = False Then Exit Sub
    ' code
End Sub

 

[birdieman]

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?