On going onto a sheet run a macro?

[Stu]

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

Thanks

 

[Chip Pearson]

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 (e-mail address removed)

 

[Harald Staff]

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.

 

[Stu]

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

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.

 

[Harald Staff]

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.

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

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

 

[Scott]

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)

 

[Tom Ogilvy]

See Chip Pearson's site for this

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