none
Excel VSTO & Design Mode RRS feed

  • Question

  • I am running some tests on adding a custom activex to an Excel worksheet via VSTO.  The control shows up correctly on the worksheet,  however it appears to leave the workbook in "Design Mode".   Thus the control is not functional until the user clicks to turn off design mode.  Am I missing something in my code when adding the activex that takes this situation into account so I can programatically turn design mode on/off.  My VSTO code to add the activex is below.

    Thanks in Advance

    Excel.

    Worksheet WS = (Excel.Worksheet)Application.ActiveSheet;

    Excel.Workbook WB = (Excel.Workbook

    )Application.ActiveWorkbook;

     

     

     

    int

    Left = 10;

     

    int

    Top = 10;

     

    int

    Width = 800;

     

    int

    Height = 700;

     

    string ProgID = "MyProgID";

     

    Excel.

    OLEObjects oleObjects1 = (Excel.OLEObjects)WS.OLEObjects(System.Type

    .Missing);

    Excel.

    OLEObject oleObj = oleObjects1.Add(ProgID, Type.Missing, Type.Missing, false, Type.Missing, Type.Missing, Type

    .Missing, Left, Top, Width, Height);

    oleObjects1.Enabled =

    true

    ;

     

    Monday, September 26, 2011 8:37 PM

Answers

  • Hi,

    AFAIK, it is not possible to turn design mode on/off by using Excel object model. This mode is designed to enable edit ActiveX control in UI, which can be done directly in code without turning this mode on. As I am not aware of how your custom ActiveX control created and how it registered in your machine, I tested you code withe some general ActiveX controls such as checkbox. However, this issue doesn't occur on my side. Maybe we can narrow down the issue caused by your custom ActiveX control.

    If the custom control is not complex enough, I would recommend you to re-design it as a UserControl, and then add the usercontrol to workbook interface, check the code:

     

                UserControl1 control = new UserControl1 ();
                Excel.Application app = Globals.ThisAddIn.Application;    
                Excel.Worksheet sheet = app.ActiveSheet as Excel.Worksheet;
             
                vstoExcel.Worksheet vstoSheet = Globals.Factory.GetVstoObject(sheet);
                Excel.Range rng = vstoSheet.Range["A1", "B4"];
                vstoSheet.Controls.AddControl(control, rng, "CustomControl");
    

    I hope this helps.

     


    Best Regards, Calvin Gao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Tuesday, September 27, 2011 10:21 AM
    Moderator

All replies

  • Forgot to mention and using Excel 2007 with an Excel Application level add-in.
    Monday, September 26, 2011 8:38 PM
  • Hi,

    AFAIK, it is not possible to turn design mode on/off by using Excel object model. This mode is designed to enable edit ActiveX control in UI, which can be done directly in code without turning this mode on. As I am not aware of how your custom ActiveX control created and how it registered in your machine, I tested you code withe some general ActiveX controls such as checkbox. However, this issue doesn't occur on my side. Maybe we can narrow down the issue caused by your custom ActiveX control.

    If the custom control is not complex enough, I would recommend you to re-design it as a UserControl, and then add the usercontrol to workbook interface, check the code:

     

                UserControl1 control = new UserControl1 ();
                Excel.Application app = Globals.ThisAddIn.Application;    
                Excel.Worksheet sheet = app.ActiveSheet as Excel.Worksheet;
             
                vstoExcel.Worksheet vstoSheet = Globals.Factory.GetVstoObject(sheet);
                Excel.Range rng = vstoSheet.Range["A1", "B4"];
                vstoSheet.Controls.AddControl(control, rng, "CustomControl");
    

    I hope this helps.

     


    Best Regards, Calvin Gao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Tuesday, September 27, 2011 10:21 AM
    Moderator
  • Hi Nemo

    You could try the Workbook.ToggleFormsDesign method. Depending on whether, as Calvin surmises, there's a problem with your ActiveX control, this may or may not help...

    Note: Please try using the code button on the edit form toolbar (second from right). It should help make your code more readable :-)


    Cindy Meister, VSTO/Word MVP
    Tuesday, September 27, 2011 12:48 PM
    Moderator
  • thank you for all the feedback.  I will certainly try the UserControl hosted on the sheet.  If I am able to get the functionality I need by following that route I will certainly do so.  My activex is really a .Net  UserControl wrapped as an ActiveX. 

    In regard to the problem I listed above, it does not seem related to the design mode or my ActiveX.   If I change the progid above to "MSCAL.Calendar" I get the same behavior.  That behavior manifests itself as a control rendered on the worksheet but is inactive.  It does not respond to any events when clicking the Activex interface.   I think the problem is related to control activation,  since if I switch to another worksheet and then back to the hosting sheet - the activex works perfectly.  the Calendar control exhibits the exact same behavior.  I believe my clicking the "design mode" button produced the same effect -  basicaly most anything I do on the menus or Excel will result in the control( and Calendar control ) functioning normally.  It only has the problem when first rendered to the active sheet.

    I will investigate the UserControl route as well - thank you!


    Captain Nemo
    Tuesday, September 27, 2011 5:22 PM
  • WOW!  I tried the user control I had wrapped as an activex,  and everything worked perfectly.  THANK YOU!   Can I add user controls like this to Excel 2003?
    Captain Nemo
    Tuesday, September 27, 2011 6:04 PM
  • Welcome.

    <<Can I add user controls like this to Excel 2003?>>

    Absolutely you can, but to create an add-in for Excel 2003, you need to install VS 2008 or VS 2005 SE. VS 2010 doesn't support Office solution for 2003.

    I hope this helps.


    Best Regards, Calvin Gao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, September 28, 2011 8:37 AM
    Moderator