Exiting design mode

Question

Hi Folks,

Is there a way to force an exit from design mode before saving? The problem is that while in design mode, VBA does not run, so saving the file while in design mode removes password protection placed through code.

Thanks!

Answer 1

Actually, all you would have to do is directly after my code snippet place a call to the macro(s) that you would like to execute.

---

"The new phonebooks are here!"

The code you posted disables the design mode control in the Visual Basic toolbar, but not the Control Toolbox toolbar.

Now I see what you are after though - if I disable design mode controls in the wookbook_open event, I don't have to worry about anyone fooling around with switching design mode on in the first place.

Now I just have to get it to work in 2003, 2007, and 2010 versions...

Answer 2

Actually, all you would have to do is directly after my code snippet place a call to the macro(s) that you would like to execute.

---

"The new phonebooks are here!"

Answer 3

You can add the following to the beforesave or the beforeclose event of the workbook...

 

 Dim dsMd As CommandBarControl

   

    Set dsMd = Application.CommandBars.FindControl(ID:=1605)

   

    If dsMd.Enabled = True Then

        dsMd.Enabled = False

    End If

---

"The new phonebooks are here!"

The problem is that when in design mode, VBA code is not processed and does not execute. So the file closes without running any code in either beforesave or beforeclose.

Perhaps a workaround is to put this in the Workbook_SheetActivate and/or Workbook_SheetDeactivate events. I will give that a try...

Thanks!

Answer 4

You can add the following to the beforesave or the beforeclose event of the workbook...

 Dim dsMd As CommandBarControl

    Set dsMd = Application.CommandBars.FindControl(ID:=1605)

    If dsMd.Enabled = True Then

        dsMd.Enabled = False

    End If

---

"The new phonebooks are here!"