Solved: Disabling Worksheet Change while macro is running

[Rejje]

Hi!

My problem in steps:

1. I trigger a macro from a button.
2. The macro selects a cell in a worksheet which is involved in a Worksheet_Change event.
3. 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]

Hi!

My problem in steps:

1. I trigger a macro from a button.
2. The macro selects a cell in a worksheet which is involved in a Worksheet_Change event.
3. 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]

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.

Example file.xlsm

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]

Exactly as GTO said.

[vba]

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[/vba]

[Rejje]

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]

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]

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]

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]

Seems to work fine for me.