Autoshape Macro

[Rob Kings]

Apologies if this is an FAQ. I couldn't find an answer when I looked in
Google.

Its difficult to explain, but here goes:

1. I have a workbook and on the some of the sheets in this workbook are
Autoshapes with macros assigned.

2. The VBA for the macros is in the sheet itself (rather than a module)

3. In code (from another Workbook) I copy the sheet into a new workbook (Be
that Book1 or whatever, it hasn't been saved yet)

4. The autoshapes don't work, because the Macro references are in the form
'Originalworkbook'!Macroname

However, 'Macroname' does exist. so how do I either:

a) Use a form of words in the macro assignment so that it looks in the
current sheet

b) Alter the assignments at runtime.

Any ideas?

Cheers

Rob

 

[Jim Rech]

You might try Edit, Links and change the link to point to the workbook
itself.

--
Jim
| Apologies if this is an FAQ. I couldn't find an answer when I looked in
| Google.
|
| Its difficult to explain, but here goes:
|
| 1. I have a workbook and on the some of the sheets in this workbook are
| Autoshapes with macros assigned.
|
| 2. The VBA for the macros is in the sheet itself (rather than a module)
|
| 3. In code (from another Workbook) I copy the sheet into a new workbook
(Be
| that Book1 or whatever, it hasn't been saved yet)
|
| 4. The autoshapes don't work, because the Macro references are in the form
| 'Originalworkbook'!Macroname
|
| However, 'Macroname' does exist. so how do I either:
|
| a) Use a form of words in the macro assignment so that it looks in the
| current sheet
|
| b) Alter the assignments at runtime.
|
| Any ideas?
|
| Cheers
|
| Rob
|
|

 

[Rob Kings]

Jim

I don't think that's going to help me. At the time I'm trying to run this
code I haven't yet saved the workbook.

The situation is this:

1. I have an XLS with some AutoShapes (basically doing sheet navigation)
These shapes run some VBA (that is in the sheet, rather than a separate
module)

2. At runtime I copy the sheet from the XLS into my active workbook. This
includes the code routines, but the autoshape has an explicit reference to
the old XLS

If I click the shape it tries to open the original file.

Since the code does exist it seems odd that I can't just call it using the
<SheetName>.<Subroutine> style, but even if you enter the macro in this
form, it still saves using the full '<Workbook>'!<SheetName>.<Subroutine>
form.

Rob

 

[Jim Rech]

Can you fix the links on the new (copied) with something like this?:

Sub FixLinks()
Dim Sh As Shape
Dim Mac As String
On Error GoTo NoBang
For Each Sh In ActiveSheet.Shapes
Mac = Sh.OnAction
Sh.OnAction = ActiveWorkbook.Name & "!" & Mid(Mac, InStr(1, Mac,
"!") + 1)
NextShape:
Next
Exit Sub
NoBang:
Resume NextShape
End Sub

--
Jim
| Jim
|
| I don't think that's going to help me. At the time I'm trying to run this
| code I haven't yet saved the workbook.
|
| The situation is this:
|
| 1. I have an XLS with some AutoShapes (basically doing sheet navigation)
| These shapes run some VBA (that is in the sheet, rather than a separate
| module)
|
| 2. At runtime I copy the sheet from the XLS into my active workbook. This
| includes the code routines, but the autoshape has an explicit reference to
| the old XLS
|
| If I click the shape it tries to open the original file.
|
| Since the code does exist it seems odd that I can't just call it using the
| <SheetName>.<Subroutine> style, but even if you enter the macro in this
| form, it still saves using the full '<Workbook>'!<SheetName>.<Subroutine>
| form.
|
| Rob
|
| | > You might try Edit, Links and change the link to point to the workbook
| > itself.
| >
| > --
| > Jim
| > | > | Apologies if this is an FAQ. I couldn't find an answer when I looked
in
| > | Google.
| > |
| > | Its difficult to explain, but here goes:
| > |
| > | 1. I have a workbook and on the some of the sheets in this workbook
are
| > | Autoshapes with macros assigned.
| > |
| > | 2. The VBA for the macros is in the sheet itself (rather than a
module)
| > |
| > | 3. In code (from another Workbook) I copy the sheet into a new
workbook
| > (Be
| > | that Book1 or whatever, it hasn't been saved yet)
| > |
| > | 4. The autoshapes don't work, because the Macro references are in the
| > form
| > | 'Originalworkbook'!Macroname
| > |
| > | However, 'Macroname' does exist. so how do I either:
| > |
| > | a) Use a form of words in the macro assignment so that it looks in the
| > | current sheet
| > |
| > | b) Alter the assignments at runtime.
| > |
| > | Any ideas?
| > |
| > | Cheers
| > |
| > | Rob
| > |
| > |
| >
| >
|
|

 

[Rob Kings]

Jim

Cool that seems to work OK. I'd guess I'd liked to have found a "non-code"
solution (since it seems a reasonable thing to want to do) but this will be
fine, since I'm running other code in any case.

Cheers

Rob