Geek Answers Handbook

Add excel vba code to button using c #

I had a question about creating an excel button and adding a vba code function to it. I created the button and module code, but I don’t know how to establish a connection between them. Can someone show me how?

my code for the 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 the 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);
+4
source share
2 answers

I searched on the Internet but didn’t find anything useful, so I cleared my mind and again focused on C # help, and I found the answer on how to do it right.

My code is:

 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";
+4
source

As far as I understand, you need an intermediate call for the code / macromodule from button when you click on the button. In this way, the code runs and does what you want.

. In the usual way, for example,

  • we add a button to the excel sheet.
  • select on_click event
  • add code like call mySub

You need to do this in C #.

Please configure for your module and manage names. Here is an example.

 //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 READ THIS MANUAL It has pretty much what you need.

According to the object, by simply connecting the subsite of a worksheet or module written in Excel from C #, you can use the run macro method.

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

Link:

+1
source

More articles:


All Articles