application.EnableEvents = false not disabling events

[Brad]

Hello, I'm having a problem that I was hoping .enableevents = false would
solve but it doesn't work at all. The events are still being triggered. The
problem is when I activate the userform, it triggers events that are
associated with the particular object. Below is my code with comments next to
the lines that have events that are being triggered: How can I stop these
events from triggering? I've tried Application.EnableEvents = False but it's
not stopping them.

Code-
Private Sub UserForm_Activate()
Application.EnableEvents = False
On Error Resume Next
Startdate.Value = Range("e56").Value 'event gets triggered
Enddate.Value = Range("h56").Value 'event gets triggered

TextBox697.Value = Sheets("data").Range("F9").Value 'event gets triggered

TextBox23.Value = Sheets("data").Range("J101").Value * 100 & "%" 'event
gets triggered

If Sheets("data").Range("abselect1").Value = "Y" Then
Abuse1.Value = True
End If
If Sheets("data").Range("inwarnty1").Value = "Y" Then
inwty1.Value = True
End If
Term1.Value = Range("trm1").Value
LP1.Value = Sheets("data").Range("line1lp").Value 'event gets triggered
SP1.Value = Sheets("data").Range("line1sp").Value 'event gets triggered
Total1.Value = Sheets("data").Range("Line1tot").Value 'event gets
triggered
EOS1.Value = Sheets("data").Range("eosdte1").Value 'event gets triggered
ComboBox1.Value = Sheets("data").Range("model1").Value 'event gets
triggered
Application.EnableEvents = True

 

[drhalter]

Brad,

I've run into this problem before. From your question it seems that events
such as TextBox23_Change() is getting triggered when your form is starting.
Is that correct?

I tried .EnableEvents as well with no success. What I ended up doing was
declaring a boolean variable at the top of the code (general, declarations
section). Then I use this variable to turn on/off sections of the code.

Dim MyEnableEvents as Boolean

UserForm_Activate()
MyEnableEvents = False
TextBox23.Value = Sheets("data").Range("J101").Value * 100 & "%"
.... YOUR CODE ...
MyEnableEvents = True
End Sub

TextBox23_Change() ... use this format in the code you don't want
triggered.
If MyEnableEvents = True Then
... Your code ...
End If
End Sub

See my discussion with Jon Peltier "suspend processing for TextBox_Change
events when initializing"

drhalter

 

[Brad]

drhalter,

That is exactly what I have textbox23.change(). I have done as you suggested
and it works perfectly, but like you said to Jon, it is a pain to have to add
that statement to all of the events.

Thank you for your help, it's much appreciated.

 

[Dave Peterson]

What event is still firing?

Application.enableevents will stop worksheet, workbook, application events. It
won't stop combobox1_change events.

But you can do your own housekeeping.

At the top of your userform module:
Option Explicit
Dim BlkEvents as boolean

'now your code
Private Sub UserForm_Activate()
'Don't hide your errors!
'On Error Resume Next

blkproc = true
Startdate.Value = Range("e56").Value 'event gets triggered
blkproc = false

blkproc = true
Enddate.Value = Range("h56").Value 'event gets triggered
blkproc = false

.....
private Textbox697_change()
if blkproc = true then exit sub
'rest of real code
end sub

And if you're doing lots in a row, you could stack them:

blkproc = true
Startdate.Value = Range("e56").Value 'event gets triggered
Enddate.Value = Range("h56").Value 'event gets triggered
TextBox697.Value = Sheets("data").Range("F9").Value
....
blkproc = false

 

[Brad]

drhalter,

Here is something strange, maybe you (or someone) can shed some light onto
this. Hopefully this makes sense because it's weird (at least to me). I did
what you suggested with the MyEnableEvents and that works fine, however if
look at my code (before using your suggestion), it triggers individual events:

Start Code'
If Sheets("data").Range("inwarnty1").Value = "Y" Then
inwty1.Value = True
End If
Term1.Value = Range("trm1").Value
LP1.Value = Sheets("data").Range("line1lp").Value 'event gets triggered
SP1.Value = Sheets("data").Range("line1sp").Value 'event gets triggered
Total1.Value = Sheets("data").Range("Line1tot").Value 'event gets
End Code'

However, in another part of the code the events don't get triggered.

Start Code'
LP5.Value = Sheets("data").Range("line5lp").Value 'event doesn't trigger
LP5 = WorksheetFunction.Text(Range("line5lp"), "$0.00") 'event doesn't
trigger
SP5.Value = Sheets("data").Range("line5sp").Value 'event doesn't trigger
SP5 = WorksheetFunction.Text(Range("line5lp"), "$0.00") 'event doesn't
trigger
Total5.Value = Sheets("data").Range("Line5tot").Value 'event doesn't
trigger
Total5 = WorksheetFunction.Text(Range("Line5tot"), "$0.00") 'event
doesn't trigger
EOS5.Value = Sheets("data").Range("eosdte5").Value 'event doesn't trigger
ComboBox5.Value = Sheets("data").Range("model5").Value 'event doesn't
trigger

end code'

Why would LP5 (etc..) not trigger, but LP1 (etc..) trigger?

Hopefully that makes sense.

Thanks,

 

[Brad]

Dave, I've tried this and it works. However, the full code is very big. If
you take the below section of code and repeat 29 additional times (the
variables changes, LP2, SP2, Total2 - LP3, SP3, Total3, through LP30, SP30,
Total30). So, I've added the errorhandling and it comes back with "Out of
stack space". I've stepped through my code and at:

LP1.Value = Sheets("data").Range("line1lp").Value

it triggers the event:

Private Sub LP1_Change()
LP1.Value = Sheets("data").Range("line1lp").Value
LP1 = WorksheetFunction.Text(Range("line1lp"), "$0.00")
end sub

Which goes into a continous loop. Any ideas?

Also, it only triggers events from the code lines that contain LP1, SP1,
Total1, through LP4, SP4, Total4.

Everything else steps through without triggering an event.

I can post the entire code if you'd like, it is very repetitive (probably
could use looping but don't know how) and very large. Let me know if it
kosher to post full code.

Thanks.

 

[Dave Peterson]

It's fine to post the full code, but if it's too complex, you may not get many
people to read through it.

But this sub:
Private Sub LP1_Change()
LP1.Value = Sheets("data").Range("line1lp").Value
LP1 = WorksheetFunction.Text(Range("line1lp"), "$0.00")
end sub
is trying to change the value of LP1 (twice!)

And as soon as the first line executes, the lp1_change event has to fire. And
over and over and over. It'll take awhile to even get to the second line of
that procedure!

Option Explicit
Dim BlkProc as boolean
Private Sub LP1_Change()
if blkproc = true then exit sub

blkproc = True
'if the cell is already displays what you want:
LP1.Value = Sheets("data").Range("line1lp").Text
'or use VBA's version of the =Text() worksheet function
'but don't use both!
LP1.Value = Format(sheets("data").range("line1lp").value, "$0.00")
blkproc = false

end sub

But I don't understand what this subroutine is doing. If the user changes the
value of whatever LP1 is, then you're overriding it with what's in that cell.
Why let the user change anything--just plop the value into a label and show it
to them.