Free Microsoft Excel 2013 Quick Reference

Design Mode in VBA


I was wondering what design mode is used for in VBA. I always have this switched off when writing programs. What does it do and when should you activate this feature?


Post your answer or comment

comments powered by Disqus

I know how to entry the design mode with VBA, but I cannot exit it with VBA code, anybody can help me?

Application.CommandBars("Control Toolbox").Controls(1).Execute

how do I turn on/off "Design mode" in Excel 2007

In earlier versions of Excel I have always done my designs without being in a
"mode". What is Design Mode and when would I use it?

I am new to Excel VBA. I have a Excel form on which i am having VB buttons. I want to disable the Design mode which will be used to view the script written for that button. I know how to Protect the script using script editor.But my need is...

1. I want to change the cursor to Hand sympol when the cursor is over that command button.
2. And if the user selects the design mode in the toolbar ,and double clicks the button , opens the vb script editor. i want this to be hidden. i want to run the code when the user clicks the button.

How to do this. Thanks in advance.


In earlier versions of Excel I have always done my designs without being in a
"mode". What is Design Mode and when would I use it?


How can I prevent tables from being calculated in Excel when I use the command "calculate" in VBA?


Hi, thanks for any assistance...

I am having a working bee with my workbook, but now design mode, in the developer tab is greyed out.. and I can not use it...

I have tried restarting , and different files, does not seem to work... can anyone help..


Hello all,

I have created a calculator which predicts a value depending on other values entered by the user.

The user first select a day from a drop down combobox (form control, not active x). They then select the number of days from another form control combox box. After pressing an ActiveX Command Button, a module calculates what days are affected (for example, if the user selects "Wednesday" and the length of 3 days, the days activated are: Wednesday, Thursday and Friday)

These strings are then displayed in ActiveX Textbox that the user cannot enter data into. The user then enters minimum and maximum temperatures for those days in other ActiveX Textboxes which have not been disabled. After pressing another ActiveX Command Button, the modules calculate a number depending on the number of days, temperatures etc...

My question is this. I have protected the sheet from users entering data into cells, I have stopped them from opening VBA, but I also want to stop them from shifting a textbox that I have on the page and from entering designmode.

I have tried to protect the sheet, but in doing so, it stops the user from selecting any of the values in the dropdown combo boxes.

I also trying to lock the design mode buttons using:

CommandBars("Control Toolbox").Controls("Design Mode").Enabled = False 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
On opening the the workbook. However, excel returns:

"Run-time error '91': Object variable or With block variable not set."

Any ideas on how to solve these? Cheers!!

Hi All,

I have created a worksheet with buttons in it. The buttons have codes attached to it. When I am sending it to other users, it opens in Design mode. And the buttons when clicked is showing the VBA code. Is there a way to lock the design mode, So that whenever a user clicks the button it doesn't show the code?


Pranob Mohanty

Is there a way to prevent this from happening? I try to get most of my layout done before getting deep into VBA, but sometimes little changes need to be made. Pretty frustrating when 30 minutes, or more, worth of work get lost because I forget to go into design mode.

Is this possibly something in my code. The only coding I have on this particular spreadsheet is a Sub Worksheet_change with 3 commands each having screenupdating = false/true before/after each command.

I only have like 1/20th of the spreadsheet made and i've already wasted like an hour from this grrrrrrrrrrrr.

My head is about to explode I hate Office 2007 so much ...

I hate junking' up the forum with such a stupid question but after an hour of frustration I give up,

Where is the design mode toolboar in Excel 2007 ?
You know, where you click and draw command buttons, text boxes, etc ?

With older versions you would click the design mode toolbox and have a panel of objects, just like in VB, but I can not find it with 07 ... What am I missing ?

Thanks !!

(I swear I think Microsoft used a bunch of 4th grade child slave labor to design all there new apps ...)


I got the following code from this forum which can open a workbook in Design mode by putting it in ThisWorkbook - Thank you very much.

Private Sub Workbook_Open()
End Sub
I am copying a sheet of workbook to create a new workbook using VBA. Original workbook should be in run mode and new workbook should be in design mode (because I don't want the codes (worksheet_change) in the sheet module to run while editing) . Can I insert above code into ThisWorkbook of new workbook while copying or is there any other way?

Thanks, Anil

Access 2000 has some significant design changes, as a result, unlike previous versions, a database must be opened in Exclusive mode before you can successfully make any design changes. Deleting an Access specific object (Forms, Reports, Macros, and Modules etc.) in a database via Automation code is considered a design change.
The OpenCurrentDatabase method allows you to specify an optional argument which will force the database to be opened in exclusive mode. You will need to modify your old code to include this argument.

    Const DB_NAME = "D:office2000OfficeSamplesNorthwind.mdb" 
    Dim objAcc As Access.Application 
    Set objAcc = New Access.Application 
    With objAcc 
        .Visible = True 
        .OpenCurrentDatabase DB_NAME, True 
        .DoCmd.DeleteObject acModule, "Module1" 
    End With 
    Set objAcc = Nothing 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
can one help me in writing the macro in vba for deleting the record connection to teradata.
thanks in advance.

I created a file in Excel97 that opens with a Userform active (the workbook itself is totally hidden). If the user clicks the 'X' on the Userform the workbook closes.
In Excel97 if I needed to make changes to the workbook or code I could open the workbook without the Userform running by pressing the Shift Key while opening the file. This doesn't seem to work in Excel2007. Does anyone know how to open Excel2007 in Design Mode?

I created a macro 'command button' that hyperlinks to another page in the workbook. It works perfectly in Design Mode. When I Exit Design Mode, the button no longer works (it does not go to the hyperlinked page.)


For some strange reason the workbook I have been using on a daily basis has suddenly decided to go into design mode and stay there. No matter how many times I click the design mode button in and out it always stays in and I can't use my just selects them??

Anyone got any ideas???

My security is set to 'low'

Thanks in advance

ack - I can't figure this out - the controls in the control toolbox are
disabled when I am in design mode. when I move the mouse over one of the
buttons I placed on the worksheet yesterday it has the circle with the slash
through it.

anyone have suggestions?


kent eilers

How can I pass Excel events to a designer form in an ActiveX DLL ?

I had created a userform in Excel VBA that contains Excel's refedit controls
to easily select cell references from Excel worksheets. It works fine when
it was executed in Excel's VBA. This userform was then added to an ActiveX
DLL and became a Designer form.

My VB6 application creates the object for this class defined in the ActiveX
DLL above in Excel. From Excel, a worksheet event would activate the loading
of the object's userform. However, when this object's Designer form show
event is executed, it does not stop to allow the user to enter and complete
its fields. I had tried the Windows API function to set it back to the
foreground but the form seems to ignore any of the Excel Events. I can't set
it to modal mode either. Can anyone tell me what's seems to be wrong ?

Thanking you in advance for any advice or help you can offer.


If you're talking about recording clicks to the VBA editor AFAIK this can't
be done. If you think about it there would be no point. The macro recorder
only concerns itself with recording your actions within the Excel worksheet
environment. As soon as you're into VBA you are free to do what you want.


Bill Lunney

"TKT-Tang" > wrote in message
> The macro recorder does not appear capable of reporting the changing
> phases of the duplex Design Mode.
> And so, please say, the VBA code required for switching in-and-out of
> the Design Mode.
> Regards.

Hi there, i have built a spreadsheet with several command buttons in for
various things, when i click on any of the buttons they do not work unless i
click the design mode button first.
Can someone please tell my why and what i can do to stop this?

Thank you,

I know that when the Design Mode is in an "ON" position and when you click on
the Combo Box button, it will automatically goes to edit mode. Is there
another reason why the Combo Box button will not work even though the Design
Mode is in an "OFF" position.?
Charles O.

I have found that even though my project (which is working perfectly on several machines) is password protected against viewing, some machiones open up automatically in design mode??

Is there any way to completely disable this??


I have an external program that sends data to excel via COM by raising events. My VBA program handles these events and needs to update cell values. Excel and/or the COM program become unstable if the user has placed excel in edit mode. Is there a way to detect from VBA that excel is in edit mode so that the I can programatically control when cells are updated based on the mode? This way, I will be handle the events as raised by the COM program but not update the cells if excel is in edit mode. TIA.

When embedding certain ActiveX controls in Excel Spreadsheet ( not in a
form ) the control is invisible in design mode. It works fine and
shows up when I toggle to Run mode but can't be found in Design mode
except by moving the mouse around very slowly and waiting for cursor to
change when over the border of the control.

Anyone have any ideas what might be the problem,
or more importantly what might be solution - how to make
the control show up ?

* * Please include a copy of this message with your reply

Jeff Bennett
Jeff @ Bennet-Tec.Com

* Bennet-Tec Information Systems, Inc
* 50 Jericho Tpk, Jericho, NY 11753
* Phone 516 997 5596, Fax - 5597
* RELIABLE Components Make You Look Sharp!
* TList/Pro * ALLText HT/Pro * MetaDraw *
* Custom Software Development Services Too.
* WWW.Bennet-Tec.Com

=================== ===================

No luck finding an answer? You could always try Google.