If you want to fire the macro when that sheet is made active, then

right click on the sheet tab of that sheet and select view code.

In the left dropdown at the top of the resulting code module, select
worksheet and in the right dropdown select Activate.

Put your code in the resulting Event procedure

Private Sub Worksheet_Activate()
MsgBox "For Editors Only", vbOKOnly, "CAUTION!!!"
End Sub

the dialog should come up whenever the sheet is activated. Is that what you
want?

for general information on events
http://www.cpearson.com/excel/events.htm

--
regards,
Tom Ogilvy



"JB2010" wrote:

Hi

thanks for that, looks good, but it isnt firing, probably because my initial
code was dump

when the specific sheet become active, i just want an OKOnly MsgBox to turn
up & issue a warning. So ive now got this....

--------------------------------------------------------------
Sub TESTM1()
If ActiveSheet.Name < ("Validation Lists") Then Exit Sub

MsgBox "For Editors Only", vbOKOnly, "CAUTION!!!"

End Sub
-------------------------------

any thoughts on how to fix this?


thanks again for your help, sorry for the rather unstimulating level of
complexity!!!





"Tom Ogilvy" wrote:

Sub MyMacro()
if Activesheet.Name < "Sheet2" then exit sub

' current code

end Sub

--
Regards,
Tom Ogilvy


"JB2010" wrote:

Hi


Ive been trawling through previous postings to try & find an answer to this
as it must be so simple, but to no avail.


All i need is the code that tells VBA "When 'Sheet 2' is active
(i.e.visable), run the macro, otherwise dont".

Sorry that this is so lame! Please let me know if you need anymore info


cheers


jb