Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro for active sheet only
I have a macro which applies to, and is recorded within, one specific
spreadsheet, says, stats.xls. I have linked it to a custom button and it works well. However, I usually have several spreadsheets open simultaneously and occasionally I inadvertently run hit the button and run the macro even when I am working on one of the other sheets. This is not disastrous but is there a way I can add some code to the macro so that it will only run when stats.xls is the active sheet 'at the front'. Thanks, V |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro for active sheet only
You could check something on that sheet (if it's nice and unique):
Option Explicit Sub YourMacro() if activesheet.range("A1").value < "this is the correct Sheet" then exit sub end if 'rest of code here End sub You could look for a nice unique header (a couple of cells???). Or a comment in a cell--or a name or anything you can think of. But my bet is that some day, you may want to run it against a worksheet where you won't find that key info. I'd just ask... Option Explicit Sub YourMacro() dim Resp as long resp = msgbox(Prompt:="You're about to run the macro that ...", _ buttons:=vbokcancel) if resp = vbcancel then exit sub end if 'rest of code here End sub Victor Delta wrote: I have a macro which applies to, and is recorded within, one specific spreadsheet, says, stats.xls. I have linked it to a custom button and it works well. However, I usually have several spreadsheets open simultaneously and occasionally I inadvertently run hit the button and run the macro even when I am working on one of the other sheets. This is not disastrous but is there a way I can add some code to the macro so that it will only run when stats.xls is the active sheet 'at the front'. Thanks, V -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro for active sheet only
You keep referring to stats.xls as a "sheet"
stats.xls is workbook, not a worksheet. In your code, point the macro to Thisworkbook and whatever sheet in the workbook you want to run the macro upon. Gord Dibben MS Excel MVP On Tue, 20 Oct 2009 23:31:06 +0100, "Victor Delta" wrote: I have a macro which applies to, and is recorded within, one specific spreadsheet, says, stats.xls. I have linked it to a custom button and it works well. However, I usually have several spreadsheets open simultaneously and occasionally I inadvertently run hit the button and run the macro even when I am working on one of the other sheets. This is not disastrous but is there a way I can add some code to the macro so that it will only run when stats.xls is the active sheet 'at the front'. Thanks, V |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro for active sheet only
Dave
Many thanks. I'll try a few experiments based on your suggestions. V "Dave Peterson" wrote in message ... You could check something on that sheet (if it's nice and unique): Option Explicit Sub YourMacro() if activesheet.range("A1").value < "this is the correct Sheet" then exit sub end if 'rest of code here End sub You could look for a nice unique header (a couple of cells???). Or a comment in a cell--or a name or anything you can think of. But my bet is that some day, you may want to run it against a worksheet where you won't find that key info. I'd just ask... Option Explicit Sub YourMacro() dim Resp as long resp = msgbox(Prompt:="You're about to run the macro that ...", _ buttons:=vbokcancel) if resp = vbcancel then exit sub end if 'rest of code here End sub Victor Delta wrote: I have a macro which applies to, and is recorded within, one specific spreadsheet, says, stats.xls. I have linked it to a custom button and it works well. However, I usually have several spreadsheets open simultaneously and occasionally I inadvertently run hit the button and run the macro even when I am working on one of the other sheets. This is not disastrous but is there a way I can add some code to the macro so that it will only run when stats.xls is the active sheet 'at the front'. Thanks, V -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro for active sheet only
Gord
Thanks - absolutely right. I'm afraid my question used some sloppy terms. V "Gord Dibben" <gorddibbATshawDOTca wrote in message ... You keep referring to stats.xls as a "sheet" stats.xls is workbook, not a worksheet. In your code, point the macro to Thisworkbook and whatever sheet in the workbook you want to run the macro upon. Gord Dibben MS Excel MVP On Tue, 20 Oct 2009 23:31:06 +0100, "Victor Delta" wrote: I have a macro which applies to, and is recorded within, one specific spreadsheet, says, stats.xls. I have linked it to a custom button and it works well. However, I usually have several spreadsheets open simultaneously and occasionally I inadvertently run hit the button and run the macro even when I am working on one of the other sheets. This is not disastrous but is there a way I can add some code to the macro so that it will only run when stats.xls is the active sheet 'at the front'. Thanks, V |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
diable macro if a certain sheet is active | Excel Discussion (Misc queries) | |||
emailing active sheet with macro | Excel Worksheet Functions | |||
macro active sheet | Excel Discussion (Misc queries) | |||
copy name from active sheet to cell - using macro or function | Excel Worksheet Functions | |||
How to change macro so it performs actions on ACTIVE sheet? | Excel Discussion (Misc queries) |