Changing button names - [resolved]

[TheFIDDLER]

Ok - new one for you guys and gals.

I have a sheet with three Form Control buttons. Each button has a macro assigned to it. There is a Back, a Next and a Print button.

I copy the sheet to a new workbook (see my previous answer to DaveBo's post for code example). Code works exceptionally well and moves the sheet to a new workbook. Now from here, I want to strip the sheet of the three Form Control Buttons.

It is a very straightforward code approach to accomplish this.
Essentially, something like, with sheets(1).Shapes("Button 2").Cut
I name the shape by name since I have lots of other shapes on the sheet that must remain in place as they are graphics and are an integral part of my presentation.

Problem is that Excel randomly renames my buttons for me while pasting. Button 38 can become Button 42. It is consistent providing I start with the same base number of buttons, but if I start another version of Excel, or open another workbook, then it increments its Button count to match, leaving my code dead in the water.

I would like suggestions as to how to code to trap just my three buttons while leaving out the rest of my shapes. Something that would be irrelevant of the button number would be nice.

[medison]

Here are a couple ways to identify the buttons. The first loop will work for buttons created from the Control Toolbox toolbar while the second loop will work for buttons created from the Forms toolbar.

It sounds like the buttons that you have are from the Forms toolbar. However, I would suggest that you replace them with buttons from the Control Toolbox. Instead of assigning a macro to the button you would add code to the button's click event. These buttons can be identified as CommandButtons as opposed to a generic msoFormControl type.


HTH





Dim o As OLEObject

For Each o In Sheet1.OLEObjects
If TypeName(o.Object) = "CommandButton" _
Then MsgBox o.Name, vbOKOnly, "Here's one!"
Next o

Set o = Nothing

'¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤

Dim s As Shape

For Each s In Sheet1.Shapes
If Left(s.Name, 7) = "Button " And s.Type = msoFormControl _
Then MsgBox s.Name, vbOKOnly, "Here's one!"
Next s

Set s = Nothing

[TheFIDDLER]

Thank you very much.