Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
When Sheet Is Active, Run Macro, Otherwise Dont
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
When Sheet Is Active, Run Macro, Otherwise Dont
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
When Sheet Is Active, Run Macro, Otherwise Dont
Hi Jb2010,
All i need is the code that tells VBA "When 'Sheet 2' is active (i.e.visable), run the macro, otherwise dont". If Activesheet.Name="Sheet 2" Then 'Run code Else 'Do not run code End If Regards, Jan Karel Pieterse Excel MVP http://www.jkp-ads.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
When Sheet Is Active, Run Macro, Otherwise Dont
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
When Sheet Is Active, Run Macro, Otherwise Dont
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
When Sheet Is Active, Run Macro, Otherwise Dont
thats brilliant. as you can tell, im relatively new to macro's, so an idiots
guide to where to point the mouse really helps. thanks very much indeed. I now understand a great deal more. cheers "Tom Ogilvy" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for active sheet only | Excel Discussion (Misc queries) | |||
macro active sheet | Excel Discussion (Misc queries) | |||
Copy from active sheet and paste into new sheet using info from cell in active | Excel Programming | |||
macro only in active sheet | Excel Programming | |||
MACRO for copying active sheet without using a certain name | Excel Programming |