Replacing macro buttons with a new set of buttons
 

I have about a hundred sheets in a workbook that each have six individual
buttons I created for running various macros.
I have since redesigned them and want to replace the old 5 buttons with 5
new ones (eliminating one button). I have grouped the new buttons so I
could just paste them as one item (hopefully using a macro. I could of
course ungroup them if this would make it any easier to work with). The
new buttons are slightly larger than the old ones and are in a slightly
different location. The new buttons are on a worksheet called "Blank".
The other worksheets have different Customer numbers as names.
What would be the easiest way to delete the old buttons and place the new
ones on each sheet?

Thanks in advance for your expert help. All you guys are great!

Jonco

Replacing macro buttons with a new set of buttons
 

Correction: I have 6 buttons I want to replace with 5 redesigned buttons.


"jonco" wrote in message
. net...
I have about a hundred sheets in a workbook that each have six individual
buttons I created for running various macros.
I have since redesigned them and want to replace the old 5 buttons with 5
new ones (eliminating one button). I have grouped the new buttons so I
could just paste them as one item (hopefully using a macro. I could of
course ungroup them if this would make it any easier to work with). The
new buttons are slightly larger than the old ones and are in a slightly
different location. The new buttons are on a worksheet called "Blank".
The other worksheets have different Customer numbers as names.
What would be the easiest way to delete the old buttons and place the new
ones on each sheet?

Thanks in advance for your expert help. All you guys are great!

Jonco

Replacing macro buttons with a new set of buttons
 

Here's what I have so far. I'm getting a "NEXT with No FOR error".
But i have a FOR.

Help please

Sub ReplaceButtons()
'
' ReplaceButtons Macro

For Each Worksheet In ThisWorkbook.Worksheets
Select Case Worksheet.Name
Case "Index", "Trans", "Customers"
'don't do anything
Case Else
Worksheet.Unprotect
CurrentSheet = ActiveSheet.Name
ActiveSheet.Unprotect
' Select old buttons to replace
ActiveSheet.Shapes.Range(Array("Button 1", "Button 2", "Button 3",
"Button 4" _
, "Button 5", "Button 6")).Select
Selection.Cut
Sheets("Blank").Select
ActiveSheet.Unprotect
ActiveSheet.Shapes("Group 15").Select
Selection.Copy
Worksheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets(CurrentSheet).Select ' Selects current customer sheet
Range("A3").Select
ActiveSheet.Paste
Worksheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

Next
MsgBox "done"

End Sub




"jonco" wrote in message
. net...
I have about a hundred sheets in a workbook that each have six individual
buttons I created for running various macros.
I have since redesigned them and want to replace the old 5 buttons with 5
new ones (eliminating one button). I have grouped the new buttons so I
could just paste them as one item (hopefully using a macro. I could of
course ungroup them if this would make it any easier to work with). The
new buttons are slightly larger than the old ones and are in a slightly
different location. The new buttons are on a worksheet called "Blank".
The other worksheets have different Customer numbers as names.
What would be the easiest way to delete the old buttons and place the new
ones on each sheet?

Thanks in advance for your expert help. All you guys are great!

Jonco

Replacing macro buttons with a new set of buttons
 

You're missing an End Select statement.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"jonco" wrote in message
. com...
Here's what I have so far. I'm getting a "NEXT with No FOR
error". But i have a FOR.

Help please

Sub ReplaceButtons()
'
' ReplaceButtons Macro

For Each Worksheet In ThisWorkbook.Worksheets
Select Case Worksheet.Name
Case "Index", "Trans", "Customers"
'don't do anything
Case Else
Worksheet.Unprotect
CurrentSheet = ActiveSheet.Name
ActiveSheet.Unprotect
' Select old buttons to replace
ActiveSheet.Shapes.Range(Array("Button 1", "Button 2",
"Button 3", "Button 4" _
, "Button 5", "Button 6")).Select
Selection.Cut
Sheets("Blank").Select
ActiveSheet.Unprotect
ActiveSheet.Shapes("Group 15").Select
Selection.Copy
Worksheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Sheets(CurrentSheet).Select ' Selects current customer
sheet
Range("A3").Select
ActiveSheet.Paste
Worksheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

Next
MsgBox "done"

End Sub




"jonco" wrote in message
. net...
I have about a hundred sheets in a workbook that each have six
individual buttons I created for running various macros.
I have since redesigned them and want to replace the old 5
buttons with 5 new ones (eliminating one button). I have
grouped the new buttons so I could just paste them as one item
(hopefully using a macro. I could of course ungroup them if
this would make it any easier to work with). The new buttons
are slightly larger than the old ones and are in a slightly
different location. The new buttons are on a worksheet called
"Blank". The other worksheets have different Customer numbers
as names.
What would be the easiest way to delete the old buttons and
place the new ones on each sheet?

Thanks in advance for your expert help. All you guys are
great!

Jonco