5

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
Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72
David Wilson
  • 51
  • 1
  • 3
  • 1
    since no code will run once DesignMode is turned on, you probably will have to alert the user to turn `DesignMode` off before leaving. For example, before turning it on, make a message box that alerts them to do so before leaving the sheet. – Scott Holtzman Nov 09 '15 at 18:05
  • 1
    Further to what @ScottHoltzman mentioned, you can also put a form control in the respective sheet and assign the `testExit` macro to it. – Siddharth Rout Nov 10 '15 at 15:20
  • 1
    In many pc's the developer tab may not be visible so asking the user to go and click in design mode may not be the best option. In such a case either follow my advice of adding a form control or paste the code in a module and assign s shortcut key to it so that user can come out of design mode by pressing that key. – Siddharth Rout Nov 10 '15 at 15:28
  • Wanted to avoid any user input as minimal excel skills must be assumed in the target group. – David Wilson Nov 10 '15 at 21:08

3 Answers3

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.

Nathan_Sav
  • 8,466
  • 2
  • 13
  • 20
  • application.ontime seems like a good fix but for me will but turn design mode on. What code are you using (or maybe where are yu placing it) to get the design mode trigger to fire? – David Wilson Nov 10 '15 at 21:06
  • That is good but doesn't solve the problem of coming out of design mode – David Wilson Nov 11 '15 at 13:08
  • Did you test with the sheet name in a hidden sheet. I tested it last night and it seemed to work for me. I'll have another look a bit later. – Nathan_Sav Nov 11 '15 at 14:13
0

I think design mode will turn the macro's off.

Nathan_Sav
  • 8,466
  • 2
  • 13
  • 20
-1

This works for me; 

 CommandBars("Exit Design Mode").Controls(1).Execute    'Enter Design Mode
 CommandBars("Exit Design Mode").Controls(1).Reset      'Exit Design Mode
mikebinz
  • 370
  • 1
  • 4
  • 17