Results 1 to 8 of 8

Thread: How to insert code inside a command button in Sheet1 and not in module?

  1. #1

    Thread Starter
    Member
    Join Date
    Apr 2019
    Posts
    54

    Smile How to insert code inside a command button in Sheet1 and not in module?

    Name:  vbforum.jpg
Views: 435
Size:  35.5 KB

    Here is my code in creating command button in excel.

    Code:
     
       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)
    What I want is to insert code in Sheet1 and not adding module 1 to inset onto it.
    Name:  vbforum2.PNG
Views: 458
Size:  4.1 KB

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: How to insert code inside a command button in Sheet1 and not in module?

    Welcome to VBForums

    Thread moved from the 'VB.Net' forum to the 'Office Development/VBA' forum.

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: How to insert code inside a command button in Sheet1 and not in module?

    you would need something more like
    Code:
     xlmod = xlworkbook.VBProject.VBComponents(xlworksheet1.Name).CodeModule
    Ln = xlmod.ProcStartLine(objBtn.Name & "_Click", vbext_pk_Proc)
    xlmod.InsertLines Ln + 1, "msgbox ""hello world""" & vbNewLine & "msgbox ""test completed"""
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  4. #4

    Thread Starter
    Member
    Join Date
    Apr 2019
    Posts
    54

    Re: How to insert code inside a command button in Sheet1 and not in module?

    Quote Originally Posted by westconn1 View Post
    you would need something more like
    Code:
     xlmod = xlworkbook.VBProject.VBComponents(xlworksheet1.Name).CodeModule
    Ln = xlmod.ProcStartLine(objBtn.Name & "_Click", vbext_pk_Proc)
    xlmod.InsertLines Ln + 1, "msgbox ""hello world""" & vbNewLine & "msgbox ""test completed"""
    Thanks my friend for your idea. When I try those lines of codes, it shows an error:Name:  error sheet1.jpg
Views: 273
Size:  8.8 KB
    Please help me to fix the error. Thank you.

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: How to insert code inside a command button in Sheet1 and not in module?

    i can not read the image
    what error do you get

    is xlworkbook a valid workbook object?
    does xlworkshhet1.name return the expected value?

    also you may need to change to ln + 2

    please note, i spent some time testing this should work as requested, to insert the code into he module, at the correct location, but i do not in any way expect the click event to work when the button is clicked, which while you did not ask for that, it may be that is the result you are looking for
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  6. #6

    Thread Starter
    Member
    Join Date
    Apr 2019
    Posts
    54

    Re: How to insert code inside a command button in Sheet1 and not in module?

    Quote Originally Posted by westconn1 View Post
    i can not read the image
    what error do you get

    is xlworkbook a valid workbook object?
    does xlworkshhet1.name return the expected value?

    also you may need to change to ln + 2

    please note, i spent some time testing this should work as requested, to insert the code into he module, at the correct location, but i do not in any way expect the click event to work when the button is clicked, which while you did not ask for that, it may be that is the result you are looking for
    I got this error: Conversion from string "xlworksheet1.name" to type Integer is not valid. Error in line xlmod = xlworkbook.VBProject.VBComponents(xlworksheet1.Name).CodeModule.

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: How to insert code inside a command button in Sheet1 and not in module?

    it worked on my test, but you can try as posted in the previous thread
    Code:
    For Each xlmod In xlworkbook.VBProject.VBComponents
        If xlmod.Name = xlworksheet1.Name Then Exit For
    Next
    note also you should avoid you application constant names (xlworkbook) as variable names as it may cause a conflict

    if you are actually trying to give the button a click event, you should probably create a class for a button withevents
    if there are to be multiple buttons then the same class can be used for all buttons by adding each instance to a collection
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  8. #8

    Thread Starter
    Member
    Join Date
    Apr 2019
    Posts
    54

    Resolved Re: How to insert code inside a command button in Sheet1 and not in module?

    Quote Originally Posted by westconn1 View Post
    it worked on my test, but you can try as posted in the previous thread
    Code:
    For Each xlmod In xlworkbook.VBProject.VBComponents
        If xlmod.Name = xlworksheet1.Name Then Exit For
    Next
    note also you should avoid you application constant names (xlworkbook) as variable names as it may cause a conflict

    if you are actually trying to give the button a click event, you should probably create a class for a button withevents
    if there are to be multiple buttons then the same class can be used for all buttons by adding each instance to a collection
    Thanks so much for your help my friend. Finally found an answer from this link: http://www.vbforums.com/showthread.p...5379071http://

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