How to temporarily disable an event? 1

[childrenfirst]

Hi,

I have two macros created for an Excel workbook. The first one is to update column AD in all worksheets in the workbook and the workbook is open (it's a Workbook_Open event). The second one is to show a popup message whenever any cell in column AD in any worksheet is selected (it's a Workbook_SheetSelectionChange even). Each macro runs perfectly when they exist independently. However, I ran into an annoying problem when I put both of them in the same workbook. The popup messages start poping up as Workbook_Open updates every row in every worksheet. I need help! I want to temporarily disable the Workbook_SheetSelectionChange event and re-enable it after all Workbopen finishes executing. Any advise will be greatly appreciated!

Thanks a bunch,
ChildrenFirst

 

[PHV]

Application.EnableEvents = False
'Your code here
Application.EnableEvents = True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[childrenfirst]

Hi PHV,

This may be a silly question, but which event should I insert the code to? Also, how do I specify which event I want to disable though (I don't want to disable the Workbook_Open event. I just want to disable the Workbook_SheetSelectionChange event until Workbook_Open event completes)!

Thank you very much for always being very helpful,
Childrenfirst

 

[PHV]

OK, I meant simply this:
Private Sub Workbook_Open()
Application.EnableEvents = False
'Your actual code here
Application.EnableEvents = True
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[childrenfirst]

Thank you, thank you, thank you, PHV! It worked very very well!

 

[duGly]

Here's a related issue and the solution I have come up with.

Issue: Many of the procedures in my UserForms trigger UserForm or control events when I don't want them to. For example, this line of code triggers the txtLastName_Change event:

    Me.txtLastName = "Jones"

It may be that I only want the event to fire when the text box is edited by the User, not when it is changed programmatically. Unfortunately, turning off the Application.EnableEvents does not prevent UserForm or control events from being triggered.

My Solution: I create a modular (or sometimes even a global) boolean variable called m_blnAuto. Before running a line of code that will trigger another event, I set m_blnAuto to TRUE. After the offending code I am sure to set it back to FALSE. Then in the triggered control's event handler I place the following line of code:

    If m_blnAuto Then Exit Sub

Even though this does not keep the event from firing, it does at least keep the event handler's code from running.

1) I hope this helps someone else with the same problem.
2) Does anyone know a better solution that would keep the event from firing all together?

 

[combo]

There are some other partial (you do not have full functionality) solutions for userform/control events:
1. Choose other event, for instance BeforeUpdate, that fires only by UI generated events.

2. Use WithEvents variables for sensitive controls and simulate WithEvents functionality, assuming you have a textbox ('txtLastName') and a checkbox ('Checkbox1') on the userform:

Private WithEvents vTxtLastName As MSForms.TextBox
Private WithEvents vUserForm1 As UserForm

Private mEnableEvents As Boolean

Private Sub CheckBox1_Click()
Me.EnableEvents = Me.CheckBox1.Value
End Sub

Private Sub UserForm_Initialize()
mEnableEvents = False
End Sub

Private Sub UserForm_Terminate()
Call SetWithEventsVariables(False)
End Sub

Private Sub vTxtLastName_Change()
MsgBox "txtLastName changed!"
End Sub

Private Sub vUserForm1_Click()
MsgBox "UserForm clicked!"
End Sub

Public Property Get EnableEvents() As Boolean
EnableEvents = mEnableEvents
End Property

Public Property Let EnableEvents(ByVal bEnableEvents As Boolean)
mEnableEvents = bEnableEvents
Call SetWithEventsVariables(mEnableEvents)
End Property

Private Sub SetWithEventsVariables(bAssignObjects As Boolean)
Select Case bAssignObjects
Case True
    Set vTxtLastName = Me.txtLastName
    Set vUserForm1 = Me
Case False
    Set vTxtLastName = Nothing
    Set vUserForm1 = Nothing
End Select
End Sub

combo