Autoshape Macro
 

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

--
Jim
"Rob Kings" wrote in message
...
| 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
|
|

Autoshape Macro
 

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" wrote in message
...
You might try Edit, Links and change the link to point to the workbook
itself.

--
Jim
"Rob Kings" wrote in message
...
| 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
|
|

Autoshape Macro
 

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
"Rob Kings" wrote in message
...
| 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" wrote in message
| ...
| You might try Edit, Links and change the link to point to the workbook
| itself.
|
| --
| Jim
| "Rob Kings" wrote in message
| ...
| | 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
| |
| |
|
|
|
|

Autoshape Macro
 

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

"Jim Rech" wrote in message
...
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
"Rob Kings" wrote in message
...
| 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" wrote in message
| ...
| You might try Edit, Links and change the link to point to the workbook
| itself.
|
| --
| Jim
| "Rob Kings" wrote in message
| ...
| | 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
| |
| |
|
|
|
|