Macro...turn off Design Mode

thedaska

New Member
Joined
Feb 20, 2008
Messages
10
Hey, I've developed a dashboard which contains buttons and dropdown. But one of the people who receives it has desing mode still switched on.

I was wondering if anyone could help me with a macro so that when the user goes into the spreadsheet, we can ensure that design mode is off and they can press the button and use the drop downs?
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Welcome to the Board!

From what I have collected this is impossible. Code cannot and will not run while in design mode and if it opens in design mode then it cannot run.

Sorry, you probably weren't looking for that, but mayeb someone can come along with something outside of Excel or tell me differently.
 
Upvote 0
So is there no way to add a macro, so when the work book opens that it turns off the design mode. So that it will ensure that they will be able to use my drop downs and buttons??
 
Upvote 0
Nope sorry. When the workbook opens if it is already in design mode it can thus run no code period. No macros will run in design mode. Its wierd that the user's workbook would be opening in design mode, unless macros are disabled? Are you sure macros are enabled? If they are then save the workbook not in design mode and re-open and it shouldn't be in design mode.
 
Upvote 0
Or just tell you user that he has to turn off design mode before he opens your spreadsheet. If they are clever enough to need design mode switched on, then they are clever enough to know how to switch it off.
 
Upvote 0
There is a way to detect it is in design mode and tell the user to turn it off...

Create a new sheet, name it DesignMode (for lack of better idea).
On that sheet put some kind of description of Why/How to turn off design mode.
Hide That Sheet

Put this code in the ThisWorkbook module.

Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("DesignMode").Visible = True
Sheets("DesignMode").Select
ThisWorkbook.Save
End Sub
 
 
Private Sub Workbook_Open()
Sheets("Sheet2").Visible = True
Sheets("Sheet2").Select
Sheets("DesignMode").Visible = False
End Sub

Change "Sheet2" to whatever sheet you want to be the default sheet selected when users open the book.

This will Save the book with "DesignMode" Sheet as the Active Selected Sheet.
So if the user opens it in Design mode, macros will be disabled, therefor user will see the DesignMode Sheet with Instructions for How/Why to turn off Design Mode.

If Design mode is already OFF, then macros are enabled and the DesignMode Sheet will be hidden, selecting the Sheet2.
 
Upvote 0
I do something similar to check to see if macros are turned on. The spreadsheet is saved with a sheet visible that says you have macros turned off. It also contains instructions on how to turn them on.

The file open macro switches sheets to the main sheet

So, if macros are turned off, the spreadsheet just loads and the sheet just sits there instructing them what to do. If macros are turned on... the open macro switches sheets so they can do some work. The close macro switches sheets back to the opening sheet (no macros) then saves the file - ready for the next person to open.

You may want to do something like that, but with instructions including design mode. (Maybe I should include information about design mode too, I didn't think about that one.)
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top