VBA Code assigned to CommandButton

mio306

New Member
Joined
Feb 21, 2011
Messages
2
In three sheets I have a button CommandButton_Click(). I wrote, in all three sheets, this code and I assigned at all the three buttons. Pressed the button that copies the active sheet a new wrokbook.
Code:
Private Sub CommandButtonSave_Click () 
... 
... 
ActiveSheet.Copy 
With ActiveSheet.UsedRange 
. Copy 
. PasteSpecial xlValues 
. PasteSpecial xlFormats 
End With 
ActiveSheet.Shapes (CommandButton1). Delete 
Application.DisplayAlerts = False 
ActiveWorkbook.SaveAs FileName: = NewName, FileFormat: = xlWorkbookNormal 
Application.DisplayAlerts = True 
ActiveWindow.Close 
..... 
..... 
End Sub
The solution I do not like it because:
1. Same code is replicated three times , written in the Sheet1, Sheet2 and Sheet3. I would write it in one place.
2. In the couple of the sheet, the macro deletes the CommandButton, but the code remains in the VBA project . I would like to have the new worbook without VBA code.
I tried to solve the problem by writing code in ThisWorbook, but I do not know how to assign it to any one of the buttons CommandButton_Click (), in three different sheet.
Someone could help to solve this problem? Thanks in advance.
Mio
 
Last edited by a moderator:

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Use Forms buttons instead. You can assign the same macro to each, and the code must be stored in a Module, and not in the sheet code area, and so will not be copied.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,119
Messages
5,640,219
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top