Macro for active sheet only

  • Thread starter Thread starter Victor Delta
  • Start date Start date
V

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
 
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
 
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
 
Back
Top