Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Autoshapes as macro buttons
Hi, I have created a model in Excel 2007 using autoshapes as macro buttons.
However, sometimes when I copy, rename or move the file, the buttons lose their macro assignment. It doesn't seem to matter when I'm using it in 2003 or 2007. Is this a bug in Excel, or should I just not use autoshapes as macro buttons? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Autoshapes as macro buttons
Hi Plum -
It's OK to use autoshapes or any shape to present the user with a visual que to fire a macro. However, assignment of a macro to any shape (even if it's a toolbar button or a button on a worksheet) needs to be handled with care or the symptom you describe can and will occur. It's not considered a 'bug', but Excel can 'lose track' of a macro that's assigned to a button or shape. For example, if a worksheet containing a shape (with and assigned macro) is copied to another workbook and then the original workbook is deleted or its name is changed, your symptom will arise. There are other causes for this problem, too, most of which occur unexpectedly. To avoid this, try putting the macro code in the worksheet module instead of a standard module. VBA always looks in this module first when a macro path is not specified, so you never have to worry about VBA not finding it (if you can click the shape, the code always resides with the shape's underlying worksheet - the shape and the code are always connected and move together so moving, renaming, and deleting files has no negative impact). A suggestion, too, is to use the "New" button when assigning the macro and pasting your code into the macro name that is provided by vba (Sub AutoShape1_Click, for example). One final word of caution: shapes can be unpredictably renamed if you do a lot of shape editing, adding shapes, grouping, ungrouping, deleting, etc. If you do a lot of shape editing, just be aware that you might (might) have to reassign the macro (using "New") if a shape_click suddenly stops working. Rest assured, however, that once you're done editing and have your final layout, the macro code and the shape will work properly. --- Jay "Plum" wrote: Hi, I have created a model in Excel 2007 using autoshapes as macro buttons. However, sometimes when I copy, rename or move the file, the buttons lose their macro assignment. It doesn't seem to matter when I'm using it in 2003 or 2007. Is this a bug in Excel, or should I just not use autoshapes as macro buttons? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Autoshapes as macro buttons
"Jay" wrote: Hi Plum - It's OK to use autoshapes or any shape to present the user with a visual que to fire a macro. However, assignment of a macro to any shape (even if it's a toolbar button or a button on a worksheet) needs to be handled with care or the symptom you describe can and will occur. It's not considered a 'bug', but Excel can 'lose track' of a macro that's assigned to a button or shape. For example, if a worksheet containing a shape (with and assigned macro) is copied to another workbook and then the original workbook is deleted or its name is changed, your symptom will arise. There are other causes for this problem, too, most of which occur unexpectedly. To avoid this, try putting the macro code in the worksheet module instead of a standard module. VBA always looks in this module first when a macro path is not specified, so you never have to worry about VBA not finding it (if you can click the shape, the code always resides with the shape's underlying worksheet - the shape and the code are always connected and move together so moving, renaming, and deleting files has no negative impact). A suggestion, too, is to use the "New" button when assigning the macro and pasting your code into the macro name that is provided by vba (Sub AutoShape1_Click, for example). One final word of caution: shapes can be unpredictably renamed if you do a lot of shape editing, adding shapes, grouping, ungrouping, deleting, etc. If you do a lot of shape editing, just be aware that you might (might) have to reassign the macro (using "New") if a shape_click suddenly stops working. Rest assured, however, that once you're done editing and have your final layout, the macro code and the shape will work properly. --- Jay "Plum" wrote: Hi, I have created a model in Excel 2007 using autoshapes as macro buttons. However, sometimes when I copy, rename or move the file, the buttons lose their macro assignment. It doesn't seem to matter when I'm using it in 2003 or 2007. Is this a bug in Excel, or should I just not use autoshapes as macro buttons? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Autoshapes as macro buttons
Hi folks, If I could open this up to include assigning macro's to custom
buttons in custom toolboxes, which is virtually the same process as assigning a macro to an autoshape, I find the problem is, when I assign a macro and the Assign macro window pops up, there is an option to select "This Workbook". When you select a macro it appears without a path but as soon as you close the window and open it again, the macro is appened with a path, and the path includes the name of the excel file. If I rename the excel file then the path is no longer valid and "marcro not found" results. Is it possible to provide only the macro name within the workbook, no path data, so it only looks inside the workbook regardless of it's current file name? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Autoshapes as macro buttons
Sorry I must have made a bad mouse click and accidentally posted this, now I
cannot figure out how to delete it (new to this forum). Please ignore this posting. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro buttons opening saved macro | Excel Discussion (Misc queries) | |||
Macro Buttons | Excel Worksheet Functions | |||
Replacing macro buttons with a new set of buttons | Excel Programming | |||
Visual Basic Autoshapes Macro | Excel Discussion (Misc queries) | |||
assigning macros to buttons or autoshapes | Excel Worksheet Functions |