Adding Code to an Existing Command Button with VBA

[jasoncw]

My Excel file has two sheets. The first sheet (we will call this sheet1) has one command button, and the second sheet (we will call this sheet2) has two command buttons. If the command button on sheet1 is clicked, it will copy the entire sheet2 to a new sheet (sheet3). Now, since the sheet has been copied to a new sheet, the command buttons in sheet3 lose their code. For example, here is the new code for one of the command buttons in sheet3:

Private Sub CommandButton2_Click()

End Sub

Now what I would like to do is insert some code into this new command button in sheet3 via the command button code from sheet1 that I originally used to copy sheet2 to sheet3. The code I would like to insert is as follows:

Application.DisplayAlerts = False
Sheets("sheet1").Visible = True
Sheets("sheet1").Select
Sheets("sheet3").delete
Application.DisplayAlerts = True

My problem is I do not know how to do this in VBA. Can someone help me out? Thanks!

[Tom Ogilvy]

see chip pearson's page on working on code with code

http://www.cpearson.com/excel/vbe.htm

--
Regards,
Tom Ogilvy

"jasoncw" <jasoncw.1kioad@excelforum-nospam.com> wrote in message
news:jasoncw.1kioad@excelforum-nospam.com...
>
> My Excel file has two sheets. The first sheet (we will call this
> sheet1) has one command button, and the second sheet (we will call this
> sheet2) has two command buttons. If the command button on sheet1 is
> clicked, it will copy the entire sheet2 to a new sheet (sheet3). Now,
> since the sheet has been copied to a new sheet, the command buttons in
> sheet3 lose their code. For example, here is the new code for one of
> the command buttons in sheet3:
>
> -Private Sub CommandButton2_Click()
>
> End Sub-
>
> Now what I would like to do is insert some code into this new command
> button in sheet3 via the command button code from sheet1 that I
> originally used to copy sheet2 to sheet3. The code I would like to
> insert is as follows:
>
> -Application.DisplayAlerts = False
> Sheets("sheet1").Visible = True
> Sheets("sheet1").Select
> Sheets("sheet3").delete
> Application.DisplayAlerts = True-
>
> My problem is I do not know how to do this in VBA. Can someone help me
> out? Thanks!
>
>
> --
> jasoncw
> ------------------------------------------------------------------------
> jasoncw's Profile:
http://www.excelforum.com/member.php...o&userid=17853
> View this thread: http://www.excelforum.com/showthread...hreadid=345555
>

[jasoncw]

Thanks for the response Tom. I did look at that page before and tried it out, but for some reason, CodeModule is not recognized by VBA as a valid variable declaration. Was this added after Office 97, which my company is using?

So I think this issue pretty much is not going to allow me to use the programming based on Pearson's site.

Is there a way to "keep" the object module code from Private Subs when copying from one sheet to a new sheet?