How do I disable and enable macros on the fly?
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
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).
You can also hold down SHIFT when you open a document to disable macros.