Transfer CommandButtons to another workbook using VBA

[marimar02]

Hello,

I'm trying to Copy 4 ActiveX CommandButtons from one Workbook, each named "CommandButton1", 2, 3 and 4"and Paste onto another. I have a code that successfully copies it over but if the Target Workbook already has a CommandButton1-4, the copied Button will continue in sequence and name itself "CommandButton5".

How can I ensure that all buttons are deleted from the Target Workbook and the new button is named the same as in the Source Workbook.

Here are the codes I use to delete all buttons in Source Workbook:

On Error Resume Next    
ActiveSheet.DrawingObjects.Visible = True
ActiveSheet.DrawingObjects.Delete
On Error GoTo 0

Here is the code to Copy buttons from Source to Target Worksheet:

Source.Sheets("Journal Entry").Activate    
ActiveSheet.Shapes.Range(Array("CommandButton1")).Select
Selection.Copy
Target.Sheets("Journal Entry").Activate
ActiveSheet.Paste
ActiveSheet.Shapes.Range(Array("CommandButton1")).Select 'this is where the code errors out since the Paste is renamed to CommandButton5
Set Button = ActiveSheet.Shapes("CommandButton1")
Button.Left = 622.5
Button.Top = 48

Thanks much...

 

[Logit]

.
https://stackoverflow.com/questions/35409343/how-do-i-delete-all-buttons-on-a-worksheet

 

[marimar02]

Thanks but I need to know how to copy buttons in sequence. Buttons are still named randomly. I.e. I'll copy/paste CommandButton1 as CommandButton1, 2 as 2, then 3 pastes as 5. Why doesn't it paste in sequence?

 

[Kyle123]

Why don't you use forms buttons? Then it doesn't matter what they're called, nor do you need to copy them

 

[marimar02]

Good point. I can rebuild but I remain puzzled why Excel keeps assigning different numbers to command buttons even if i specify the correct name.