PDA

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?

GTO
06-09-2011, 05:42 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?
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.