vba code to assign macros

John

New Member
Joined
Mar 12, 2002
Messages
24
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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Hi John

Not to sure I follow but try this

With Sheet1
.Shapes("Button 1").OnAction = "MyMacro1"
.Shapes("Button 2").OnAction = "MyMacro2"
End With
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Hi John

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

_________________
Kind Regards,<font size=+2><font color="red"> I<font color="blue">van<font color="red"> F M</font color="blue">oala</font></font></font>
<font color="green">[url]http://www.gwds.co.nz/ - Under Construction[/url]
This message was edited by Ivan F Moala on 2002-04-02 21:32
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

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
Back
Top