?
Solved

Excel VBA Copy CommandButton - Backend code

Posted on 2010-01-04
3
Medium Priority
?
845 Views
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
        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
0
Comment
Question by:jamiepryer
3 Comments
 
LVL 26

Expert Comment

by:EDDYKT
ID: 26170505
you may need to create the new button instead of copy

ie

http://www.mrexcel.com/archive/Controls/6145.html
0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 1500 total points
ID: 26170624
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
 

Author Closing Comment

by:jamiepryer
ID: 31672344
cheers
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Script to copy or move mouse-selected collection of files plus targets referenced by shortcuts (.lnk) The purpose of this article is to help illuminate the real challenges and options available (where they may exist) for utilizing simple scriptin…
When it comes to writing scripts for a Client/Server computing environment it is essential to consider some way of enabling the authentication functionality within a script. This sort of consideration mainly comes into the picture when we are dealin…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

850 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