5 Replies - 220 Views - Last Post: 15 April 2019 - 11:13 PM Rate Topic: -----

#1 dpointer   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 40
  • Joined: 06-March 09

How to put a vba code inside command button in excel sheet?

Posted 13 April 2019 - 12:23 AM

Here is my code in creating and command button in sheet1 and adding code in module:
   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.

Is This A Good Question/Topic? 0
  • +

Replies To: How to put a vba code inside command button in excel sheet?

#2 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14995
  • View blog
  • Posts: 59,870
  • Joined: 12-June 08

Re: How to put a vba code inside command button in excel sheet?

Posted 13 April 2019 - 06:49 AM

I am not quite sure if I follow..

File -> options.. make sure 'developer tab' is on.
In said tab is a macro editor.
Open that up and create your button, the event, and dump the code.

----
Also seen here: https://www.codeproj...d-button-of-exc
Was This Post Helpful? 0
  • +
  • -

#3 dpointer   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 40
  • Joined: 06-March 09

Re: How to put a vba code inside command button in excel sheet?

Posted 13 April 2019 - 07:07 AM

I already created a commandbutton1 in sheet1 through vb.net. Also I already added a macro/vba code but in Module only.My only problem is on how to insert the macro/vba code inside the commandbutton1 click event.
Was This Post Helpful? 0
  • +
  • -

#4 dpointer   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 40
  • Joined: 06-March 09

Re: How to put a vba code inside command button in excel sheet?

Posted 13 April 2019 - 07:18 AM

My friend thanks for the idea but I know that already. What I need is inserting/adding vba/macro code inside the commandbutton1 click event. The link that you showed to me is the link that the posted question is mine. I hope you can give me another idea about this. Thanks

View Postmodi123_1, on 13 April 2019 - 01:49 PM, said:

I am not quite sure if I follow..

File -> options.. make sure 'developer tab' is on.
In said tab is a macro editor.
Open that up and create your button, the event, and dump the code.

----
Also seen here: https://www.codeproj...d-button-of-exc


My friend thanks for the idea but I know that already. What I need is inserting/adding vba/macro code inside the commandbutton1 click event. The link that you showed to me is the link that the posted question is mine. I hope you can give me another idea about this. Thanks 
Was This Post Helpful? 0
  • +
  • -

#5 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14995
  • View blog
  • Posts: 59,870
  • Joined: 12-June 08

Re: How to put a vba code inside command button in excel sheet?

Posted 13 April 2019 - 08:56 AM

Please do not create duplicate topics.

Here's information on inserting an activex control.
https://support.offi...ad-9c72c843a283
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14995
  • View blog
  • Posts: 59,870
  • Joined: 12-June 08

Re: How to put a vba code inside command button in excel sheet?

Posted 15 April 2019 - 11:13 PM

@DharamGusai15 - keep the help in the public forum for everyone's benefit.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1