none
Command button error RRS feed

  • Question

  • Hi all,

    I have a problem with adding a command button to VSTO project. It is coming up with an error but only if the code is activate in a certain way.

    Below is the extract from the activate sheet event:

    private void SheetActivate(object Sh)
            {
                
                Class1 c3 = new Class1();
                c3.TestCode();
            }

    Below is the extract from the ribbon button:

    public void TestCode(Office.IRibbonControl Control)
            {
                Class1 c3 = new Class1();
                c3.TestCode();
    
            }


    These are both just calling the following:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using Excel = Microsoft.Office.Interop.Excel;
    using MSForms = Microsoft.Vbe.Interop.Forms;
    using Microsoft.Vbe.Interop;
    using Microsoft.VisualBasic.CompilerServices;
    
    
    namespace Smart_View
    {
        class Class1
        {
            public void TestCode()
            {
                MSForms.CommandButton CmdBtn;
                Excel.Application app = Globals.ThisWorkbook.Application;
                Excel.Workbook wb = app.ActiveWorkbook;
                Excel.Worksheet ws = wb.Worksheets[5];
                Excel.Shape cmdButton = ws.Shapes.AddOLEObject("Forms.CommandButton.1", Type.Missing, false, false, Type.Missing, Type.Missing, Type.Missing, 200, 100, 100, 100);
                cmdButton.Name = "btnClick";
                CmdBtn = (MSForms.CommandButton)NewLateBinding.LateGet(ws, null, "btnClick", null, null, null, null);
                CmdBtn.Caption = "Click Me";
                CmdBtn.Click += new Microsoft.Vbe.Interop.Forms.CommandButtonEvents_ClickEventHandler(CmdBtn_Click);
                app.Visible = true;
                app.UserControl = true;
    
            }
            static void CmdBtn_Click()
            {
                System.Windows.Forms.MessageBox.Show("I am called from C#");
            }
        }
    }

    Now when I click the ribbon control it works perfectly, but when I try using the activate sheet event i get the following error:

    System.MissingMemberException occurred
      HResult=0x80131512
      Message=Public member 'btnClick' on type 'Worksheet' not found.
      Source=<Cannot evaluate the exception source>
      StackTrace:
    <Cannot evaluate the exception stack trace>

    Please help.... I can't figure out why calling it from the sheet activation is causing the problem.

    Many thanks,

    Joe

    Monday, October 9, 2017 4:13 PM

Answers

  • Hello,

    The code works fine for me in SheetActivate event.

    According to your code, it seems that you create a document level customization for a workbook or template instead of add-in. I suggest you use the following code to add the shape and get the command button at ThisWorkbook_Startup event and register event at SheetActivate event.

            MSForms.CommandButton CmdBtn;
            private void ThisWorkbook_Startup(object sender, System.EventArgs e)
            {
                Excel.Application app = Globals.ThisWorkbook.Application;
                Excel.Workbook wb = app.ActiveWorkbook;
                Excel.Worksheet ws = wb.Worksheets[5];
                Excel.Shape cmdButton = ws.Shapes.AddOLEObject("Forms.CommandButton.1", Type.Missing, false, false, Type.Missing, Type.Missing, Type.Missing, 200, 100, 100, 100);
                cmdButton.Name = "btnClick";
                CmdBtn = (MSForms.CommandButton)NewLateBinding.LateGet(ws, null, "btnClick", null, null, null, null);
                CmdBtn.Caption = "Click Me";
                CmdBtn.Click += new Microsoft.Vbe.Interop.Forms.CommandButtonEvents_ClickEventHandler(CmdBtn_Click);
                ThisApplication.SheetActivate += ThisApplication_SheetActivate;
            }
    
            private void ThisApplication_SheetActivate(object Sh)
            {
                CmdBtn.Click -= new Microsoft.Vbe.Interop.Forms.CommandButtonEvents_ClickEventHandler(CmdBtn_Click);
                CmdBtn.Click += new Microsoft.Vbe.Interop.Forms.CommandButtonEvents_ClickEventHandler(CmdBtn_Click);
            }
    
            static void CmdBtn_Click()
            {
                System.Windows.Forms.MessageBox.Show("I am called from C#");
            }

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 10, 2017 6:40 AM
    Moderator

All replies

  • Hello,

    The code works fine for me in SheetActivate event.

    According to your code, it seems that you create a document level customization for a workbook or template instead of add-in. I suggest you use the following code to add the shape and get the command button at ThisWorkbook_Startup event and register event at SheetActivate event.

            MSForms.CommandButton CmdBtn;
            private void ThisWorkbook_Startup(object sender, System.EventArgs e)
            {
                Excel.Application app = Globals.ThisWorkbook.Application;
                Excel.Workbook wb = app.ActiveWorkbook;
                Excel.Worksheet ws = wb.Worksheets[5];
                Excel.Shape cmdButton = ws.Shapes.AddOLEObject("Forms.CommandButton.1", Type.Missing, false, false, Type.Missing, Type.Missing, Type.Missing, 200, 100, 100, 100);
                cmdButton.Name = "btnClick";
                CmdBtn = (MSForms.CommandButton)NewLateBinding.LateGet(ws, null, "btnClick", null, null, null, null);
                CmdBtn.Caption = "Click Me";
                CmdBtn.Click += new Microsoft.Vbe.Interop.Forms.CommandButtonEvents_ClickEventHandler(CmdBtn_Click);
                ThisApplication.SheetActivate += ThisApplication_SheetActivate;
            }
    
            private void ThisApplication_SheetActivate(object Sh)
            {
                CmdBtn.Click -= new Microsoft.Vbe.Interop.Forms.CommandButtonEvents_ClickEventHandler(CmdBtn_Click);
                CmdBtn.Click += new Microsoft.Vbe.Interop.Forms.CommandButtonEvents_ClickEventHandler(CmdBtn_Click);
            }
    
            static void CmdBtn_Click()
            {
                System.Windows.Forms.MessageBox.Show("I am called from C#");
            }

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 10, 2017 6:40 AM
    Moderator
  • Thanks Celeste as always :-)
    Thursday, October 12, 2017 11:20 PM