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

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

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

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

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