Results 1 to 4 of 4

Thread: Can you add a macro to Excel worksheet button through VB.NET?

  1. #1

    Thread Starter
    Member
    Join Date
    Apr 2019
    Posts
    54

    Can you add a macro to Excel worksheet button through VB.NET?

    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.

  2. #2
    Addicted Member
    Join Date
    Nov 2011
    Posts
    229

    Re: Can you add a macro to Excel worksheet button through VB.NET?

    You can create a code module with an event procedure for your button and then insert your lines of code. The create event proc returns a value which is the first line of the code module.The following is from an online tutorial. If you temporarily comment the last line of this code the VBA editor will remain open and you will be able to view the results of inserting the lines of code.


    Code:
    Dim LineOne As Long
    
     With myWorkBook.VBProject.VBComponents.Item(myWorkSheet.CodeName).CodeModule
         LineOne = .CreateEventProc("Click", "commandbutton1") + 1
         .InsertLines(LineOne, "Sheets.Add After:=Sheets(Sheets.Count)")
     End With
         myWorkBook.VBE.MainWindow.Visible = False

  3. #3

    Thread Starter
    Member
    Join Date
    Apr 2019
    Posts
    54

    Re: Can you add a macro to Excel worksheet button through VB.NET?

    Quote Originally Posted by Mc_VB View Post
    You can create a code module with an event procedure for your button and then insert your lines of code. The create event proc returns a value which is the first line of the code module.The following is from an online tutorial. If you temporarily comment the last line of this code the VBA editor will remain open and you will be able to view the results of inserting the lines of code.


    Code:
    Dim LineOne As Long
    
     With myWorkBook.VBProject.VBComponents.Item(myWorkSheet.CodeName).CodeModule
         LineOne = .CreateEventProc("Click", "commandbutton1") + 1
         .InsertLines(LineOne, "Sheets.Add After:=Sheets(Sheets.Count)")
     End With
         myWorkBook.VBE.MainWindow.Visible = False
    Thanks my friend and my problem has solved. Thumbs up

  4. #4

    Thread Starter
    Member
    Join Date
    Apr 2019
    Posts
    54

    Resolved Re: Can you add a macro to Excel worksheet button through VB.NET?

    Solved! Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width