Macro for active sheet only

[Victor Delta]

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]

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

 

[Gord Dibben]

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

 

[Victor Delta]

Dave

Many thanks. I'll try a few experiments based on your suggestions.

V

 

[Victor Delta]

Gord

Thanks - absolutely right. I'm afraid my question used some sloppy terms.

V