Syntax to Run Macro in ActiveWorkbook

[Trevor Williams]

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

 

[Jacob Skaria]

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

 

[Trevor Williams]

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]

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
menu>Insert module and place your macro...

If this post helps click Yes

 

[Trevor Williams]

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]

Try

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

If this post helps click Yes

 

[Dave Peterson]

Sometimes the filename needs to be surrounded by apostrophes.

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

It won't hurt if they're not required.

=======
You may want to consider replacing the shape with a commandbutton from the
Control Toolbox toolbar. The code will have to change (use the
Commandbutton1_click event), but you won't have to reassign anything when you
copy|move the sheet.

 

[Trevor Williams]

Ah ha! -- cracked it. Thanks for your help Jacob.
Trevor

 

[keiji kounoike]

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