How do I disable and enable macros on the fly?

Question

I would like to test an Excel VBA app I made.
However the VBA code messes around with the visibility of cells and that's a pest when editing the sheet.

Is there an option is enable and disable macro's on the fly without having to

Close the sheet
Change the macro settings
Reopen the sheet
Close the sheet
Change the macro settings.
etc.

Answer 1

As far as I know, you can't enable / disable macros from an opened workbook on the fly.
Yet, you shouldn't have to because macros are only triggered thanks to a user click.

The only case I would see is for the Event Procedures (Worksheet_Change or else).
You could then create procedures to activate / deactivate events and call them from buttons in your worksbook:

Sub enableEvents()
    Application.EnableEvents = True
End Sub

Sub disableEvents()
    Application.EnableEvents = False
End Sub

You can also try these tips from Chris Pearson website using global vars you would change depending on your needs:

Public AbortChangeEvent As Boolean

And check if afterwards:

Private Sub Worksheet_Change(ByVal Target As Range)
    If AbortChangeEvent = True Then
        Exit Sub
    End If
    '
    ' rest of code here
    '
End Sub

Answer 2

To disable macros on the fly, use "Application.EnableEvents = False" via the Immediate window in the VBA editor (and "Application.EnableEvents = True" to turn them back on).

Answer 3

You can also hold down SHIFT when you open a document to disable macros.