Excel VBA Copy CommandButton - Backend code

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
        MainWB.Sheets("Instructions").CommandButton1.Copy
        TmpWbInstrSht.Select
            ActiveSheet.Paste
            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
        Range("A1").Select
    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?

thanks
jamiepryerAsked:
Who is Participating?
 
Chris BottomleyConnect With a Mentor Software Quality Lead EngineerCommented:
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

Regards,

chris_bottomley
    Set mainWB = ThisWorkbook
    Set TmpWbInstrSht = mainWB.Sheets(2)
    If mainWB.Sheets("Instructions").CommandButton1.Visible = True Then
        mainWB.Sheets("Instructions").CommandButton1.Copy
        TmpWbInstrSht.Select
            ActiveSheet.Paste
            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
        Range("A1").Select
        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

0
 
EDDYKTCommented:
you may need to create the new button instead of copy

ie

http://www.mrexcel.com/archive/Controls/6145.html
0
 
jamiepryerAuthor Commented:
cheers
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.