Excel VBA Copy CommandButton - Backend code

Posted on 2010-01-04
Last Modified: 2012-05-08
Hi All,

ive got a bit of code, that will create a new workbook, then copy over a button from my master workbook, to this new workbook.

    If MainWB.Sheets("Instructions").CommandButton1.Visible = True Then
            ActiveSheet.Shapes("CommandButton1").Left = MainWB.Sheets("Instructions").CommandButton1.Left
            ActiveSheet.Shapes("CommandButton1").Top = MainWB.Sheets("Instructions").CommandButton1.Top
            ActiveSheet.Shapes("CommandButton1").Height = MainWB.Sheets("Instructions").CommandButton1.Height
            ActiveSheet.Shapes("CommandButton1").Width = MainWB.Sheets("Instructions").CommandButton1.Width
    End If

however the problem is that this button doesnt have any code behind it?
I want it to just do a simple print page, however im not sure how to get my code, to add some code to this sheet?

Question by:jamiepryer
    LVL 26

    Expert Comment

    you may need to create the new button instead of copy

    LVL 59

    Accepted Solution

    Hello jamiepryer,

    Try a block as below and sampled in the snippet ... replace the msgbox with your code and note the double quotes are to produce a single quote in the output string.
            With CodeModule
                .insertlines .countoflines + 1, "Private Sub CommandButton1_Click()"
                .insertlines .countoflines + 1, "    'SOmething or other"
                .insertlines .countoflines + 1, "End Sub"
            End With


        Set mainWB = ThisWorkbook
        Set TmpWbInstrSht = mainWB.Sheets(2)
        If mainWB.Sheets("Instructions").CommandButton1.Visible = True Then
                ActiveSheet.Shapes("CommandButton1").Left = mainWB.Sheets("Instructions").CommandButton1.Left
                ActiveSheet.Shapes("CommandButton1").Top = mainWB.Sheets("Instructions").CommandButton1.Top
                ActiveSheet.Shapes("CommandButton1").Height = mainWB.Sheets("Instructions").CommandButton1.Height
                ActiveSheet.Shapes("CommandButton1").Width = mainWB.Sheets("Instructions").CommandButton1.Width
            With ThisWorkbook.VBProject.VBComponents.Item(TmpWbInstrSht.CodeName).CodeModule
                .insertlines .countoflines + 1, "Private Sub CommandButton1_Click()"
                .insertlines .countoflines + 1, "    msgbox ""hi Me"""
                .insertlines .countoflines + 1, "End Sub"
            End With
        End If

    Open in new window


    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Introduction During my participation as a VBScript contributor at Experts Exchange, one of the most common questions I come across is this: "I have a script that runs against only one computer. How can I make it run against a list of computers in …
    Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now