Using Autoshapes as macro buttons

[Guest]

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?

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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?

 

[Guest]

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.