Add excel vba code to button using c#

Question

I have a question about creating excel button and adding vba code function on it. I have created a button and module code but don't know how to make relation between them. Can anyone show me how?

my code for Button:

 Excel.Shape btn = xlWorkSheet5.Shapes.AddOLEObject("Forms.CommandButton.1", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, 300, 10, 150, 22);

 Excel.OLEObject sheetBtn = (Excel.OLEObject)xlWorkSheet5.OLEObjects(btn.Name);
            sheetBtn.Object.GetType().InvokeMember("Caption", System.Reflection.BindingFlags.SetProperty, null, sheetBtn.Object, new object[] { "Calculate Bus Load" });

and code for module:

 String sCode = "Sub main()\r\n" +
                "   MsgBox \"Hello world\"\r\n" +
                "end Sub";

 VBA.VBComponent oModule = xlWorkBook.VBProject.VBComponents.Add(VBA.vbext_ComponentType.vbext_ct_StdModule);
            oModule.Name = "Module1";
            oModule.CodeModule.AddFromString(sCode);

            xlWorkBook.VBProject.VBComponents.Item(1).CodeModule.AddFromString(sCode);

+1 for the interesting question. Perhaps you could later update your question with the final answer to benefit anyone like you and me. :-) — bonCodigo
– bonCodigo, Commented Dec 14, 2012 at 17:10

Le Viet Hung · Accepted Answer · 2014-03-24 17:05:05Z

4

I have searched in Internet but didn't find anything usefull, so i cleared my mind and focused once more with c# help and I found an answer how to do it properly.

My Code:

String updateBT "...// macro code for button";   
VBA.VBComponent oModule1 = xlWorkBook.VBProject.VBComponents.Add(VBA.vbext_ComponentType.vbext_ct_StdModule);
oModule1.Name = "Update";
oModule1.CodeModule.AddFromString(updateBT);

Excel.Shape btn2 = xlWorkSheet1.Shapes.AddFormControl(Excel.XlFormControl.xlButtonControl, 150, 5, 150, 22);
btn2.Name = "Update";
btn2.OnAction = "... // name of your macro code";
btn2.OLEFormat.Object.Caption = "... // Button name";

edited Mar 24, 2014 at 17:05

user2140173

answered Dec 19, 2012 at 14:14

Le Viet Hung

5294 gold badges10 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

bonCodigo · Accepted Answer · 2012-12-14 14:55:57Z

1

As far as I understand, you need the intermediate call to code/macro module from the button when you click on the button. So the code gets triggered and does what you want it to do.

In usual manner, for e.g.

we add a button in Excel Sheet
choose on_click event
add a code like call mySub

You need to do that within C#.

Please adjust for your module and control names. Here is a sample.

//Within your above code add,

sheetBtn.Click += new MSForms.CommandButtonEvents_ClickEventHandler(sheetBtn_Click);

}

//button click event triggers

void sheetBtn_Click()
{
     call subMain
     // try a test using : MessageBox.Show("button test!");  
}

** PLEASE TRY THIS TUTORIAL OUT It has pretty much what you need.

As per the subject on just invoking a sheet sub or module sub written in Excel from C#, you may use run macro method.

//instead of this.application, you call refer to the Excel app object
this.Application.Run("yourMacroName",missing,missing........)

Reference:

edited Dec 14, 2012 at 14:55

answered Dec 14, 2012 at 14:02

bonCodigo

14.4k1 gold badge52 silver badges93 bronze badges

3 Comments

Le Viet Hung Over a year ago

Something like ` MSForms.CommandButton cmdButton = sheetBtn.Object; cmdButton.Click += new MSForms.CommandButtonEvents_ClickEventHandler(cmdButton_Click);` and then ` void cmdButton_Click() { }` ? but what should i write in cmdbutton_click?

bonCodigo Over a year ago

Do you have the freedom to use a already created button in Excel? Where that button event already has the call subMain in Excel?

Le Viet Hung Over a year ago

hmm i don't think so, how can i active it?

Collectives™ on Stack Overflow

Add excel vba code to button using c#

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related