View Full Version : Solved: Disabling Worksheet Change while macro is running
Rejje
06-09-2011, 02:07 PM
Hi!
My problem in steps:
I trigger a macro from a button.
The macro selects a cell in a worksheet which is involved in a Worksheet_Change event.
Worksheet_Change event starts running and interupts the macro triggered by the button click. THIS I DON'T WANT HAPPENING!How can I disable all events connected to the worksheet while the macro is running?
Hi!
My problem in steps:
I trigger a macro from a button.
The macro selects a cell in a worksheet which is involved in a Worksheet_Change event.
Worksheet_Change event starts running and interupts the macro triggered by the button click. THIS I DON'T WANT HAPPENING!How can I disable all events connected to the worksheet while the macro is running?
1 & 3: Use Application.EnableEvents = (False or True) in your macro that is called from the button.
2: It is doubtful that you need or want to be selecting cells in your code. If you include your code and/or a small example workbook that demonstrates what the code is doing, we might be able to offer improvements...
Hope that helps,
Mark
Rejje
06-10-2011, 01:19 AM
Ok - I didn't get it working proberly as no matter where I placed Application.EnableEvents = True calling the other sub "HideRow" didn't occur (Application.EnableEvents = False I placed after dim).
I have included a very, very simplified example workbook to show the problem.
6104
Please ask me in case I need to explain furthermore. My vba skills are still pretty basic so I guess you will find a much better solution for this example.
Rejje
Bob Phillips
06-10-2011, 01:31 AM
Exactly as GTO said.
Sub EraseAllData()
Dim answer As VbMsgBoxResult
On Error GoTo Err_handler
answer = MsgBox("Do you really want to erase all data?", vbYesNo)
If answer = vbYes Then
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
If Range("WB_IS_TEMPLATE") = True Then
Range("ALL_DATA").ClearContents
Else
Range("ALL_DATA_EXCEPT_ORG_NUMBER").ClearContents
End If
Call HideRow ' this sub is involved in many events
If Range("WB_IS_TEMPLATE") = True Then
Range("ORG_NUMBER").Select
MsgBox ("All data has been erased!")
Else
Range("NAME").Select
MsgBox ("All data except org.number has been erased!")
End If
End If
Sub_exit:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Exit Sub
Err_handler:
MsgBox ("An error occured - the data could not be erased!")
Resume Sub_exit
End Sub
Rejje
06-10-2011, 02:16 PM
Yepp - it works in the example file. I should have checked that as well with enable events but I didn't.
Strange thing though: It doesn't work with the original file! Well; the fontcolor doesn't change but hiding the row won't happen. I just don't have a clue why.
GTO: You wrote I probably didn't have to use .Select. How is that done with erasing the data. I would really like to see an example!
Bob Phillips
06-10-2011, 10:34 PM
GTO: You wrote I probably didn't have to use .Select. How is that done with erasing the data. I would really like to see an example!
Look at my code, I changed the selects.
Bob Phillips
06-10-2011, 10:35 PM
Yepp - it works in the example file. I should have checked that as well with enable events but I didn't.
Strange thing though: It doesn't work with the original file! Well; the fontcolor doesn't change but hiding the row won't happen. I just don't have a clue why.
Is it possible to post that workbook?
Rejje
06-11-2011, 06:10 AM
I didn't check the code properly, I admit. However when I go with the Range().ClearContents I get an error because these ranges include merged cells. It seems Range().ClearContents doesn't work with merged cells.
Is there a workaround?
Bob Phillips
06-11-2011, 07:53 AM
Seems to work fine for me.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.