Activate / deativate design mode on worksheet selection

Question

Really struggling with this one. I am trying to take a workbook out of design mode when a user leaves a particular sheet. I have the co which will run from buttons taking the sheet in and out of design mode.

Now I want to fire these on worksheet activate / deactivate events. Worksheet activate is fine and enters design mode.

However, VBA has a problem coming out of design mode from code. Am I missing something. Or is there a totally different way to approach this.

Thanks D

Sub testEnter()
    EnterExitDesignMode True
End Sub

Sub testExit()
    EnterExitDesignMode False
End Sub

Sub EnterExitDesignMode(bEnter As Boolean)
Dim cbrs As CommandBars
Const sMsoName As String = "DesignMode"

    Set cbrs = Application.CommandBars
    If Not cbrs Is Nothing Then
        If cbrs.GetEnabledMso(sMsoName) Then
            If bEnter <> cbrs.GetPressedMso(sMsoName) Then
                cbrs.ExecuteMso sMsoName
                Stop
            End If
        End If
    End If
End Sub

Answer 1

Just been playing and you could try this.

When selecting the sheet, put the name of the sheet in a public var, then start an application.ontime to check each second whether the active sheet name is different to this var, if so the ontime call turns design mode off, if not then retains design mode (your exisiting code). Just tried a quick demo in design in VB IDE and In Design on the Ribbon and seems like it will work.

Cheers.

Something like

Sub EnterExitDesignMode(bEnter As Boolean)
Dim cbrs As CommandBars
Const sMsoName As String = "DesignMode"
    Application.OnTime Now + TimeSerial(0, 0, 1), "TIMER_TEST"
    Set cbrs = Application.CommandBars
    If Not cbrs Is Nothing Then
        If cbrs.GetEnabledMso(sMsoName) Then
            If bEnter <> cbrs.GetPressedMso(sMsoName) Then
                cbrs.ExecuteMso sMsoName
                Stop
            End If
        End If
    End If
End Sub

Public Sub TIMER_TEST()
If ActiveSheet.Name = strSheetName Then
    EnterExitDesignMode True
Else
End If
End Sub

You'll need to put the sheet name in a hidden sheet, as the variable gets dropped.

Not fully tested, but should assist.

THanks.