Toggle Design Mode in Excel by AddIn

Discussion in 'Excel Programming' started by korav@inbox.ru, Nov 20, 2007.

  1. Guest

    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.
     
    , Nov 20, 2007
    #1
    AJ Morales likes this.
    1. Advertisements

  2. Guest

    On 20 ÎÏÑÂ, 15:04, wrote:
    > 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.
     
    , Nov 27, 2007
    #2
    1. Advertisements

  3. Chip Pearson Guest

    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)


    <> wrote in message
    news:...
    On 20 ÎÏÑÂ, 15:04, wrote:
    > 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, Nov 28, 2007
    #3
  4. Guest

    On 28 ÎÏÑÂ, 03:46, "Chip Pearson" <> wrote:
    > 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. :(
     
    , Nov 29, 2007
    #4
  5. AJ Morales

    Joined:
    Oct 11, 2015
    Messages:
    2
    Likes Received:
    0
    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!
     
    AJ Morales, Oct 11, 2015
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. carl

    Remove Excel AddIn from AddIn List !! Help

    carl, Dec 8, 2003, in forum: Excel Programming
    Replies:
    2
    Views:
    258
    Guest
    Dec 8, 2003
  2. Rob

    Use Toolbar Button to Toggle Addin

    Rob, Jan 27, 2005, in forum: Excel Programming
    Replies:
    4
    Views:
    126
  3. Craig
    Replies:
    0
    Views:
    113
    Craig
    Mar 16, 2005
  4. Hexman
    Replies:
    1
    Views:
    147
    Hexman
    Dec 22, 2005
  5. clara

    Switch between Design Mode and "Running" Mode

    clara, Mar 19, 2007, in forum: Excel Programming
    Replies:
    1
    Views:
    158
    OssieMac
    Mar 19, 2007
  6. Gerry
    Replies:
    0
    Views:
    162
    Gerry
    Oct 31, 2007
  7. Dwipayan Das
    Replies:
    0
    Views:
    166
    Dwipayan Das
    Apr 17, 2009
  8. DzednConfsd

    Do I need DLL or COM Addin, Excel Addin

    DzednConfsd, Jul 4, 2010, in forum: Excel Programming
    Replies:
    1
    Views:
    129
    Akihito Yamashiro
    Jul 5, 2010
Loading...