Try

ActiveSheet.Shapes("Rectangle 1").OnAction = _
"'" & Activeworkbook.name & "'!sheet2.runthis"

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


"Trevor Williams" wrote:

The newMacroName is in the Worksheet Module of the newSheet.

I've tested the theory on a separate file, which works well - the only
difference being that it lists the macro with the sheet index i.e.

ActiveSheet.Shapes("Rectangle 1").OnAction = _
Activeworkbook.name & "!sheet2.runthis"

However, when I apply the same theory to the main file I get a run time
error 1004
The macro 'my file name.xls!sheet35.newMacroName' cannot be found.

The macro is listed in the Macros List (within Excel) until I try and run it
whilst recording another macro. -- very odd.

Any more thoughts?

Thanks
Trevor

"Jacob Skaria" wrote:

ActiveWorkbook will look at the modules within the current workbook for the
macro? I am not sure where you have placed the macros. In VBE,from
menuInsert module and place your macro...

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


"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