Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax to Run Macro in ActiveWorkbook
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax to Run Macro in ActiveWorkbook
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax to Run Macro in ActiveWorkbook
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax to Run Macro in ActiveWorkbook
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax to Run Macro in ActiveWorkbook
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax to Run Macro in ActiveWorkbook
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax to Run Macro in ActiveWorkbook
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 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 -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax to Run Macro in ActiveWorkbook
Ah ha! -- cracked it. Thanks for your help Jacob. Trevor "Jacob Skaria" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax to Run Macro in ActiveWorkbook
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help with macro syntax | Excel Discussion (Misc queries) | |||
Error When using ActiveWorkBook.SaveAs and EnableEvents in same macro | Excel Programming | |||
Macro for importing a fixed width text file into the activeworkbook | Excel Programming | |||
Need syntax for RUNning a Word macro with an argument, called from an Excel macro | Excel Programming | |||
Not apply macro to every worksheet in activeworkbook | Setting up and Configuration of Excel |