none
Disable Design Mode - Excel VBA RRS feed

  • Question

  • Is there any way to disable Design Mode within microsoft excel?

    The reason:

    I am making a workbook template in which I want to restrict saving.  If the user presses any save button, it will prompt for a password.  If the PW is correct, it will save; if incorrect, it will cancel.  I know this can be done easily with Excel's protect workbook function without macros. However, I need it to do various things or save in various locations based on what password is entered.  

    What I have shown below is in the "ThisWorkbook" Code page:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 'Used so worksheet is not saved before needed.

    Dim X As Integer On Error GoTo NotSaved X = InputBox("Please enter Password", "Saving Protection") If X = 524 Then 'nothing - saves workbook Else MsgBox "Incorrect Password! Workbook will not be saved", vbOKOnly, "SAVING NOT ALLOWED" Cancel = True End If Exit Sub NotSaved: MsgBox "Incorrect Password! Workbook will not be saved", vbOKOnly, "SAVING NOT ALLOWED" Cancel = True End Sub


    This works nice and fine for what I need (with several other ElseIf password functions that are not included here).  However, if the user simply enables Design Mode, then this is all useless as he can save the workbook however he wants while in Design Mode.  Is there any way to prohibit Design Mode? or possibly allow this code to still be active while Design Mode is active? 

    Wednesday, October 10, 2018 3:56 PM

Answers

  • Editing the CustomUI is a bit of a learning curve, though just for this not so much to do. Start with the link below and get the CustomUI editor referred to (hmm, not sure if the link is for the original 2007 version or the update for 2010 and later versions).

    https://www.rondebruin.nl/win/s2/win001.htm

    The following works for me in 2007

    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
      <commands>
        <command idMso="DesignMode" enabled="false" />
      </commands>
    </customUI>
    If this is added to an xlsm it will change the Ribbon only when the workbook is active, which from what you describe is what it should be added to. And 'yes' to your question about on a network opened by any user.


    • Marked as answer by A.R. Martin Monday, October 22, 2018 11:09 PM
    Monday, October 22, 2018 7:09 PM
    Moderator

All replies

  • Hi A.R. Martin,

    The following code is to change the state of Design mode, please try the code as below:

    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

    For more information, please review the following links:

    vba code to turn off 'design mode' in excel application

    how to disable the design mode

    How to Disable the Design mode of EXcel Sheet

    Hopefully it helps you.

    Best Regards,

    Lina


    MSDN Community Support Please remember to click &amp;quot;Mark as Answer&amp;quot; the responses that resolved your issue, and to click &amp;quot;Unmark as Answer&amp;quot; if not. This can be beneficial to other community members reading this thread.

    Thursday, October 11, 2018 1:48 AM
  • Hi A.R Martin,

    Thanks for your asking. Please remember to mark the replies(Include your solution) as answers if they helped and please help us close the thread.

     

    Thank you for understanding. If you have any question, or update, please feel free to let us know.

     

    I wish you a happy life!

    Best Regards,

    Lina


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Friday, October 12, 2018 6:54 AM
  • Lina,

    Am I just supposed to paste that code into the ThisWorkbook page?  I did that and it does not disable Design Mode.  I am still able to access Design Mode and bypass any macro coding.

    Thank you

    Thursday, October 18, 2018 3:10 PM
  • What I am looking for is an option to completely disable the Design Mode by making it impossible to use.  Either by disabling the button, or having a code that will automatically turn it off immediately after it is turned on.  Is that possible?
    Thursday, October 18, 2018 8:04 PM
  • Hi A.R. Martin,

    Base on my test, the following code that will turn it off immediately after it is turned on:

    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

    If it still does not work, please refer to the link as below:

    Macro...turn off Design Mode

    Hopefully it helps you.

    Best Regards,

    Lina



    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.





    • Edited by Lina-MSFT Monday, October 22, 2018 6:03 AM
    Friday, October 19, 2018 6:56 AM
  • Lina,

    The TestExit() and TestEnter() macros will turn Design Mode on and off if I run the respective macro.  But if I press the Design Mode button on the toolbar, it stays active and does not turn off.  Probably cannot be done because once Design Mode is active, it disables all code, so there would be no way to run a code to turn it off?

    Saturday, October 20, 2018 1:17 AM
  • Hi A.R. Martin,

    Sorry for the late reply.

    Don't you want to run the macro every time like the screenshot below? Or as Andy said, you can use customUI to display DesignMode.

    Best Regards,

    Lina


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.


    • Edited by Lina-MSFT Monday, October 22, 2018 6:22 AM
    Monday, October 22, 2018 5:59 AM
  • Design mode disables the workbook's events such as BeforeSave, you can't then make it 'active' as you put it. You could go some way to 'prohibit' design mode with a little xml in the CustomUI, eg

      <commands>
        <command idMso="DesignMode" enabled="false" />
      </commands>
    Or you maybe hide the 'Controls' group in the Developer tab altogether

    The initiated might find some other way to enter design mode, if that's a concern you'll need a bit more xml and a callback for this control to trap its state changing. 


    Monday, October 22, 2018 11:43 AM
    Moderator
  • I need to be able to disable Design Mode altogether.  Being able to activate Design Mode should be impossible.  I guess this is not possible within the constraints of VBA, since Design Mode completely disables all VBA coding. 

    The CustomUI method may work.  I am not familiar at all on how to use this, so I may need some pointers.

    Also, this is a network file.  Many different users will be accessing this on their own user accounts.  Would the Custom Interface apply to any user who opens this spreadsheet?

    Thank you.

    Monday, October 22, 2018 3:13 PM
  • Editing the CustomUI is a bit of a learning curve, though just for this not so much to do. Start with the link below and get the CustomUI editor referred to (hmm, not sure if the link is for the original 2007 version or the update for 2010 and later versions).

    https://www.rondebruin.nl/win/s2/win001.htm

    The following works for me in 2007

    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
      <commands>
        <command idMso="DesignMode" enabled="false" />
      </commands>
    </customUI>
    If this is added to an xlsm it will change the Ribbon only when the workbook is active, which from what you describe is what it should be added to. And 'yes' to your question about on a network opened by any user.


    • Marked as answer by A.R. Martin Monday, October 22, 2018 11:09 PM
    Monday, October 22, 2018 7:09 PM
    Moderator
  • After fooling around with that CustomUI editor, I finally figured it out.  It works great! :)   Thank you very much!  It completely disables the access of Design Mode from the Ribbon.  

    That is good enough for what I need.  One thing I noticed, though, is that Design Mode can still be accessed if someone pulls up the VBA editor window. (I'd post a picture but this dumb website won't let me).  However, if anyone is smart enough to actually get this far, they deserve to be able to pass my coding anyway. LOL!

      

    But Removing Design Mode Access from the ribbon like this should solve my problems.  Thanks again.  You also introduced me to the wonderful world of CustomUI.  I've been researching it more and it seems really interesting!  Opens the door to many possibilities!

    Monday, October 22, 2018 11:09 PM