vba code to assign macros

[John]

I am having trouble re-assigning macros to buttons(shapes?) using vba.
I have tried using the following:
ActiveSheet.Shapes("Button 3").Select
Selection.OnAction = "Data_1"
ActiveSheet.Shapes("Button 1").Select
Selection.OnAction = "Data_2"

I created these buttons from the forms toolbar in excel.
Some sheets have 6 or more buttons.
This works fine on the original worksheet but when I copy that sheet to another book the names "Button 3” change e.g button 3 might become button 5.
Is there any method I can use in vba to do this.

Thanks

[Dave Hawley]

Hi John

Not to sure I follow but try this

With Sheet1
.Shapes("Button 1").OnAction = "MyMacro1"
.Shapes("Button 2").OnAction = "MyMacro2"
End With

[John]

Thanks Dave

The problem was not in getting the macro to run but rather in that the button names changed. e.g on one sheet when I right click on the button the name in the top left command bar shows as button 22. When I copy this sheet the name changes to button 4. After playing with this sheet I discovered that every time I copied it it kept the button 4 name, so I have changed my macro accordingly. Interestingly not all buttons on a page change name.

Regards
John

[Ivan F Moala]

On 2002-04-01 21:00, John wrote:
Thanks Dave

The problem was not in getting the macro to run but rather in that the button names changed. e.g on one sheet when I right click on the button the name in the top left command bar shows as button 22. When I copy this sheet the name changes to button 4. After playing with this sheet I discovered that every time I copied it it kept the button 4 name, so I have changed my macro accordingly. Interestingly not all buttons on a page change name.

Regards
John

Hi John, Explicitly nameing your buttons
will take care of the copying.
eg Right click then select the Name dropdown
list to the left of the formula bar.

Type in a meaningful name eg

btn_Format , btn_Clear etc.

Copying these buttons will retain there name

[John]

Thanks Ivan

I had tried naming buttons this way but they had always reverted to the original name. I have tried again using your suggestion and it works fine. On reflection it may have been I was trying to give it a new button number rather than a name and it is possible a button with the same number existed on the worksheet.

Thanks for the useful and helpful advise

Regards
John

[Ivan F Moala]

Hi John

Just realised....another Kiwi
Haven't seen many NZ'ers @ this Board
Welcome

_________________
Kind Regards,
Ivan F Moala
http://<font color="green"><a href="...r Construction

[ This Message was edited by: Ivan F Moala on 2002-04-02 21:32 ]