Toggle Design Mode in Excel by AddIn

[korav]

How could AddIn toggle Design mode in Excel? I'm talking about some
functionality like Workbook.ToggleFormsDesignMode for Excel 2007. How
could it possible for older versions?

Thank you in advance.

 

[korav]

How could AddIn toggle Design mode in Excel? I'm talking about some
functionality like Workbook.ToggleFormsDesignMode for Excel 2007. How
could it possible for older versions?

Thank you in advance.

So, we've found it out. There is an example of code in C++ for our
AddIn. It works fine for Excel 2003 and 2007:


Excel::_ApplicationPtr pApp = NULL;

pApp = m_pParentApp;

if (pApp == NULL)
{
MessageBox(NULL, "Can't get Excel::_Application interface", "Test
Addin", MB_SETFOREGROUND);
}
else
{
try
{
Excel::_WorkbookPtr wb_ptr;

wb_ptr = pApp->GetActiveWorkbook();

wb_ptr->ToggleFormsDesign();
}
catch (_com_error err)
{
MessageBoxW(NULL, err.Description().GetBSTR(), L"Test Addin",
MB_SETFOREGROUND);
}
}



Thanks for attention, we hope it'll be useful for community.

 

[Chip Pearson]

It is very simple in VBA. The following procs will turn design mode on or
off.

Sub TurnOffDesignMode()
Const DESIGN_MODE_ID As Long = 1605&
Dim Ctrl As Office.CommandBarButton
Set Ctrl = Application.CommandBars.FindControl(ID:=DESIGN_MODE_ID)
With Ctrl
If .State = msoButtonDown Then
.Execute
End If
End With
End Sub

Sub TurnOnDesignMode()
Const DESIGN_MODE_ID As Long = 1605&
Dim Ctrl As Office.CommandBarButton
Set Ctrl = Application.CommandBars.FindControl(ID:=DESIGN_MODE_ID)
With Ctrl
If .State = msoButtonUp Then
.Execute
End If
End With
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

How could AddIn toggle Design mode in Excel? I'm talking about some
functionality like Workbook.ToggleFormsDesignMode for Excel 2007. How
could it possible for older versions?

Thank you in advance.

So, we've found it out. There is an example of code in C++ for our
AddIn. It works fine for Excel 2003 and 2007:


Excel::_ApplicationPtr pApp = NULL;

pApp = m_pParentApp;

if (pApp == NULL)
{
MessageBox(NULL, "Can't get Excel::_Application interface", "Test
Addin", MB_SETFOREGROUND);
}
else
{
try
{
Excel::_WorkbookPtr wb_ptr;

wb_ptr = pApp->GetActiveWorkbook();

wb_ptr->ToggleFormsDesign();
}
catch (_com_error err)
{
MessageBoxW(NULL, err.Description().GetBSTR(), L"Test Addin",
MB_SETFOREGROUND);
}
}



Thanks for attention, we hope it'll be useful for community.

 

[korav]

It is very simple in VBA. The following procs will turn design mode on or
off.

Thanks a lot for your participation!

Yes, we've seen the similar solution in C++


//Application.CommandBars.FindControl(ID:=1605).Execute

_variant_t vtOptional(DISP_E_PARAMNOTFOUND, VT_ERROR);

_CommandBars oBars(oApp.GetCommandBars());

CommandBarControl oCtrl(

oBars.FindControl(

vtOptional,

_variant_t(1605L),

vtOptional,

vtOptional));

if (oCtrl)

{

oCtrl.Execute();

}

It isn't real good, IMHO. There are two caveats at least, I think.

The first caveat is connected to "magic numbers" i.e. button's ID.
Did anybody from M$ promise to keep it unchanged forever?

The second one is about ideology of emulation of button click. If we
use it from the macro, which was built in particular workbook, it
works in properly way. But our AddIn could be used without any
relation to the active now workbook (yes, in our example we used the
active workbook, but it was done only for clarification of code).


And one more question for you about Design Mode detection. I
understand, it`s meaningless question for VBA, but, for our case it is
possible.
So, in our AddIn we want to control state of particular workbook - is
it in Design Mode? We've written this code:

Excel::_WorkbookPtr wb_ptr;
......

VBIDE::_VBProjectPtr ptrVBProj;
// ptrVBProj = wb_ptr->GetVBProject();
hr = wb_ptr->get_VBProject(&ptrVBProj);

if(SUCCEEDED(hr))
{
VBIDE::vbext_VBAMode mode;
hr = ptrVBProj->get_Mode(&mode);
if(SUCCEEDED(hr))
{
switch(mode)
{
case VBIDE::vbext_vm_Run:
MessageBox(NULL, "IDE in Run mode", "My Addin", MB_SETFOREGROUND);
break;
case VBIDE::vbext_vm_Break:
MessageBox(NULL, "IDE in Break mode", "My Addin",
MB_SETFOREGROUND);
break;
case VBIDE::vbext_vm_Design:
MessageBox(NULL, "IDE in Design mode", "My Addin",
MB_SETFOREGROUND);
break;
}
}

}
else
{
MessageBox(NULL, "Please Enable \"Trust access to Visual Basic
Project\" option\n under Tools\\Macro\\Security menu.", "My Addin",
MB_SETFOREGROUND);
}


It works, but it always returns VBIDE::vbext_vm_Design without any
relation to the REAL state of the Workbook.

 

[AJ Morales]

Thanks Chip, your code example gave me a contingency option for an input template I am creating. Some of my form list boxes are 'inactive' until I toggle the Design mode button off/on. I can't explain why!