temporarily stop a Private Sub Worksheet_Open

[still learning]

Happy New Year

Could I have a macro that would temporarily stop a Private Sub Worksheet_Open macro? And then have it usable again before its saved and closed.
I have a few workbooks that have >>Open macros on the sheets that will freeze a title row and puts the cursor on the first blank cell in a column (usually A)

When I want to rework the sheet and have to go to other sheets in the workbook and then back to the first one, it would be nice to click on an icon on the ribbon to temporarily stop the >>Open macro and go back to where I was and not have to contend with moving the cursor where I need it. Then when I am done I can make the open macro usable again before I save and close the sheet . I can add it to the close and save macro that I use.

What I do now is open the VBA after I open the workbook and suspend the macro. Sometimes I forget to make it usable again after closing it


mike

 

[shknbk2]

Using this page as inspiration I came up with the following.

I assume the "Open" macro is the Activate macro for the sheet. As far as I know, Open is only available at a Workbook level.

Add the If Not code to your Worksheet code (you didn't provide it, so I made up something that works well with this entire method)

Private Sub Worksheet_Activate()
    If Not gbHiddenState Then
        Range("A1").Select
    End If
End Sub

Put this code into a Standard Code Module

Public gbHiddenState As Boolean

Dim MyRibbon As IRibbonUI

'Callback for TbtnToggleHideColumn onAction
Sub TbtnToggleIsClicked(control As IRibbonControl, pressed As Boolean)
  '--switch state of global variable
  gbHiddenState = Not gbHiddenState
End Sub

'Callback for TbtnToggleHideColumn getPressed
Sub GetPressed(control As IRibbonControl, ByRef returnedVal)
  returnedVal = gbHiddenState
End Sub

Insert this into the Workbook's customUI.xml (Note that this adds the toggle button at the end of the View tab. You can play around with this to make a new custom tab, put it somewhere else, customize the label and other text, etc. by following online guidance)

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon>
<tabs>
<tab idMso="TabView">
<group id="Group1" label="Custom">
<toggleButton id="TbtnToggleHideColumn"
        label="Stop Sh1 Activate"
        screentip="MyScreenTip"
        size="large"
        onAction="TbtnToggleIsClicked"
            getPressed="GetPressed"
            imageMso="ColumnSettingsMenu"/>
</group>
</tab>
</tabs>
</ribbon>
</customUI>

When you open the workbook each time, the toggle (and the boolean) are false so the activate code works first time. Toggling the button to the True position prevents the Activate code from running.