Is it possible to run a macro when a sheet it clicked on to be viewed?
If so how do I do it?

Thanks
--
Stu

Stu,

You can use the Activate event procedure. For example, put the following
code in the ThisWorkbook code module:


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
MsgBox "You activated: " & Sh.Name
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"Stu" wrote in message
...
Is it possible to run a macro when a sheet it clicked on to be viewed?
If so how do I do it?

Thanks
--
Stu

Hi Stu

In its simplest form; rightclick the Sheet tab, choose View Code, paste this in:

Private Sub Worksheet_Activate()
Call MyMacro
End Sub

Sub MyMacro()
MsgBox "Yo"
End Sub

It runs when you click the sheet's tab. If you need this code to run when activating one
of several windows then you also need to program the Workbook_WindowActivate event in
ThisWorkbook module. Post back if that's relevant and troublesome at the same time.

--
HTH. Best wishes Harald
Followup to newsgroup only please.

"Stu" wrote in message ...
Is it possible to run a macro when a sheet it clicked on to be viewed?
If so how do I do it?

Thanks
--
Stu

Can you run the macro from a module or do you have to run it from that
sheets code?

Thanks
--
Stu
"Harald Staff" wrote in message
...
Hi Stu

In its simplest form; rightclick the Sheet tab, choose View Code, paste
this in:

Private Sub Worksheet_Activate()
Call MyMacro
End Sub

Sub MyMacro()
MsgBox "Yo"
End Sub

It runs when you click the sheet's tab. If you need this code to run when
activating one
of several windows then you also need to program the
Workbook_WindowActivate event in
ThisWorkbook module. Post back if that's relevant and troublesome at the
same time.

--
HTH. Best wishes Harald
Followup to newsgroup only please.

"Stu" wrote in message
...
Is it possible to run a macro when a sheet it clicked on to be viewed?
If so how do I do it?

Thanks
--
Stu

You can (perhaps even should) put MyMacro on a standard module and call it from anything
anywhere. But the activate event must stay where it is. But events code (like Activate)
lives in their spesific modules, here the sheet module.

--
HTH. Best wishes Harald
Followup to newsgroup only please.

"Stu" wrote in message ...
Can you run the macro from a module or do you have to run it from that
sheets code?

Thanks
--
Stu
"Harald Staff" wrote in message
...
Hi Stu

In its simplest form; rightclick the Sheet tab, choose View Code, paste
this in:

Private Sub Worksheet_Activate()
Call MyMacro
End Sub

Sub MyMacro()
MsgBox "Yo"
End Sub

It runs when you click the sheet's tab. If you need this code to run when
activating one
of several windows then you also need to program the
Workbook_WindowActivate event in
ThisWorkbook module. Post back if that's relevant and troublesome at the
same time.

--
HTH. Best wishes Harald
Followup to newsgroup only please.

"Stu" wrote in message
...
Is it possible to run a macro when a sheet it clicked on to be viewed?
If so how do I do it?

Thanks
--
Stu

Hello,

I have been trying to add this type of code to a sheet
when the sheet is built using VBA modules. I do not see
how to plug the "Private Sub Worksheet_Activate()" code
onto the new sheet module. I need to have this completly
invisible to the user.

How can we reference the new sheet module programatically?


Any ideas appreciated.

thank you,
Scott

-----Original Message-----
You can (perhaps even should) put MyMacro on a standard
module and call it from anything
anywhere. But the activate event must stay where it is.
But events code (like Activate)
lives in their spesific modules, here the sheet module.

--
HTH. Best wishes Harald
Followup to newsgroup only please.

"Stu" wrote in message
...
Can you run the macro from a module or do you have to
run it from that
sheets code?

Thanks
--
Stu
"Harald Staff" wrote in message
...
Hi Stu

In its simplest form; rightclick the Sheet tab,
choose View Code, paste
this in:

Private Sub Worksheet_Activate()
Call MyMacro
End Sub

Sub MyMacro()
MsgBox "Yo"
End Sub

It runs when you click the sheet's tab. If you need
this code to run when
activating one
of several windows then you also need to program the
Workbook_WindowActivate event in
ThisWorkbook module. Post back if that's relevant and
troublesome at the
same time.

--
HTH. Best wishes Harald
Followup to newsgroup only please.

"Stu" wrote in message
...
Is it possible to run a macro when a sheet it
clicked on to be viewed?
If so how do I do it?

Thanks
--
Stu








.

See Chip Pearson's site for this

http://www.cpearson.com/excel/vbe.htm

--
Regards,
Tom Ogilvy

Scott wrote in message
...
Hello,

I have been trying to add this type of code to a sheet
when the sheet is built using VBA modules. I do not see
how to plug the "Private Sub Worksheet_Activate()" code
onto the new sheet module. I need to have this completly
invisible to the user.

How can we reference the new sheet module programatically?


Any ideas appreciated.

thank you,
Scott

-----Original Message-----
You can (perhaps even should) put MyMacro on a standard
module and call it from anything
anywhere. But the activate event must stay where it is.
But events code (like Activate)
lives in their spesific modules, here the sheet module.

--
HTH. Best wishes Harald
Followup to newsgroup only please.

"Stu" wrote in message
...
Can you run the macro from a module or do you have to
run it from that
sheets code?

Thanks
--
Stu
"Harald Staff" wrote in message
...
Hi Stu

In its simplest form; rightclick the Sheet tab,
choose View Code, paste
this in:

Private Sub Worksheet_Activate()
Call MyMacro
End Sub

Sub MyMacro()
MsgBox "Yo"
End Sub

It runs when you click the sheet's tab. If you need
this code to run when
activating one
of several windows then you also need to program the
Workbook_WindowActivate event in
ThisWorkbook module. Post back if that's relevant and
troublesome at the
same time.

--
HTH. Best wishes Harald
Followup to newsgroup only please.

"Stu" wrote in message
...
Is it possible to run a macro when a sheet it
clicked on to be viewed?
If so how do I do it?

Thanks
--
Stu








.