This may be different from your situation.
but this might be some hints for your case.

Write the code below into standard module in FileA

Sub Macro_update()
Dim tmpwb As Workbook
Worksheets("newSheet").Copy
Set tmpwb = ActiveWorkbook
tmpwb.ActiveSheet.Shapes("myShp").OnAction = _
tmpwb.Name & "!" & ActiveSheet.CodeName & ".TheNewMacroName"
End Sub

and write the code below into Worksheets("newSheet") in FileA

Sub TheNewMacroName()
MsgBox "hello"
End Sub

and run the macro Macro_update.

Keiji

Trevor Williams wrote:
Hi Jacob -- thanks for the quick response.

Unfortunately the code you've supplied doesn't work in this instance.

The code seems to execute ok as it does not throw up a error message, but it
doesn't apply the new macro name. If I add in an On Error Goto command
before it, it will execute the error handler.

A bit more background to the way the code is structured:
FileA contains the code, and the new sheet template.
FileB is opened via the code, (and is the activeworkbook), and has the new
sheet copied into it.
At this point there are 2 versions of the newMacroName available -- one in
FileA & one in FileB.
When the code changes the OnAction event of myShp it needs to select the
macro from FileB. If I omit any reference to a workbook and just use the
newMacroName then it assigns the macro from FileA.

Could the fact that there are 2 versions of the same macro available be the
reason it's not updating it?

Thanks again

Trevor

"Jacob Skaria" wrote:

Dim strWB As String
strWB = ActiveWorkbook.Name
ActiveSheet.Shapes("myShp").OnAction = _
strWB & "!TheNewMacroName"

OR

ActiveSheet.Shapes("myShp").OnAction = _
ActiveWorkbook.Name & "!TheNewMacroName"

If this post helps click Yes
---------------
Jacob Skaria


"Trevor Williams" wrote:

Hi All

I have a macro that copies a sheet containing another macro to the
ActiveWorkbook.

I need to update the OnAction command of a shape to run the macro attached
to the new sheet. I can get it working using absolute values, but need to
change the workbook part to ActiveWorkbook -- but can't suss out the syntax.

Here's the absolute line:
ActiveSheet.Shapes("myShp").OnAction = _
"'my file name.xls'!TheNewMacroName"

I need something like this:
ActiveSheet.Shapes("myShp").OnAction = _
ActiveWorkbook.name!TheNewMacroName

TIA
Trevor Williams