Make macro-button sheet reference relative
 

I have a button that calls a macro stored on a worksheet, so that when the
sheet is copied, the macro is copied with it. However, each time the sheet
is copied, the button in the new sheet retains a reference to the macro in
the old workbook. I have to manually right click on the button - Assign
Macro - Repoint to macro in current sheet. Is there a way to set up the
button so that it simply calls the macro stored in its parent sheet object
and not the workbook from which it was copied?

Thank-you

Make macro-button sheet reference relative
 

I have never seen that before.
Whenever I copy a sheet that contains a button with an associated macro
(even if i copy it to another workbook), the button always uses the
macro on its sheet.

I know I haven't been any help.....but i can't even think what would
make this happen.

..........A thought, are you using the button on the Forms toolbar. If
so try using the button on the Control Toolbox toolbar as you do not
assign a macro but always looks at the code on the associated sheet.

Let us know.

Donna

Make macro-button sheet reference relative
 

There are two kinds of buttons that you can use in Excel and they each work a
little diferently. The command button off of the the forms toolbar acts more
like a shape than a button. This type of button attaches to a macro within a
module (so it is handy if you want to create multiple sheets with the same
button all referencing the same macro). The other type of button (the one
Donna is alluding too) comes off of the control toolbox toolbar. It is much
more familiar to those of us who first learned VB6. if you right click on
this type of button you get a choice of View Properites and View Code. if you
are familiar with VB6 the view properties is going to look very familiar.
Click on View code and it will take you into the code which is embeded
directly in the sheet. The code for this button is specific to this sheet.
(this is the type you want)
--
HTH...

Jim Thomlinson


"Perez" wrote:

I have a button that calls a macro stored on a worksheet, so that when the
sheet is copied, the macro is copied with it. However, each time the sheet
is copied, the button in the new sheet retains a reference to the macro in
the old workbook. I have to manually right click on the button - Assign
Macro - Repoint to macro in current sheet. Is there a way to set up the
button so that it simply calls the macro stored in its parent sheet object
and not the workbook from which it was copied?

Thank-you

Make macro-button sheet reference relative
 

Jim...much better explained than I could of done.