How to put a VBA code inside command button of excel sheet using VB.NET?

Question

0.00/5 (No votes)

See more:

VS2010

Here is my code in creating a command button in sheet1 and adding code in module:

VB

Dim xlMod As Microsoft.Vbe.Interop.VBComponent

Dim objBtn As Object
       Dim celLeft As Integer
       Dim celTop As Integer
       Dim celWidth As Integer
       Dim celHeight As Integer


       celLeft = xlWorkSheet1.Range("d5").Left
       celTop = xlWorkSheet1.Range("d5").Top
       celWidth = xlWorkSheet1.Range("d5").Width
       celHeight = xlWorkSheet1.Range("d5").Height



       objBtn = xlWorkSheet1.OLEObjects.Add(ClassType:="Forms.Commandbutton.1", link:=False, _
           displayasicon:=False, Left:=celLeft, Top:=celTop, Width:=celWidth, Height:=celHeight)

       xlWorkSheet1.commandbutton1.caption = "Generate Sheet 2"
              xlWorkSheet1.commandbutton1.Height = 33
               xlWorkSheet1.commandbutton1.Width = 125
       xlWorkSheet1.commandbutton1.font.size = "12"

xlMod = xlWorkBook.VBProject.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule)
      
       Dim codeString As New StringBuilder
       codeString.AppendLine("Public Sub SaySomething()")
       codeString.AppendLine("MsgBox ""Hello""")
       codeString.AppendLine("End Sub")
      
       xlMod.CodeModule.AddFromString(codeString.ToString)

All I want is to put a vba code inside the sheet1 commandbutton1_click event and not in other Module.

Please help me.

What I have tried:

VB

xlMod = xlWorkBook.VBProject.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule)
      
       Dim codeString As New StringBuilder
       codeString.AppendLine("Public Sub SaySomething()")
       codeString.AppendLine("MsgBox ""Hello""")
       codeString.AppendLine("End Sub")
      
       xlMod.CodeModule.AddFromString(codeString.ToString)

Posted 12-Apr-19 21:24pm

kyrons

Updated 13-Apr-19 5:55am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Patrice T · Answer 1 · 2019-04-13T05:55:00

Quote:
How to put a VBA code inside command button of excel sheet using VB.NET?

"<vba code="" inside="" command="" button"="" does="" not="" exist.="" a="" button="" can="" only="" call="" subroutine="" by="" its="" name.
<pre="" lang="vb">
xlWorkSheet1.commandbutton1.caption = "Generate Sheet 2"
xlWorkSheet1.commandbutton1.Height = 33
xlWorkSheet1.commandbutton1.Width = 125
xlWorkSheet1.commandbutton1.font.size = "12"

You are already setting some properties of commandbutton1, linking some code to a button event is just setting another property, why is it so difficult?