How do I run a macro when I change worksheets

December 6, 2010 at 22:59:50
Specs: Windows XP
I would like to run a macro when I select a specific worksheet in Excel 2000. It is basically a lookup and sum macro but I would like the "summary report" sheet to automatically update whenever it is selected.

See More: How do I run a macro when I change worksheets

Report •


#1
December 7, 2010 at 02:13:46
Use the Workbook_SheetActivate event found in the ThisWorkbook module.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  If Sheet.Name = "mySheet" Then
   'Your code
  End If
End Sub

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#2
December 7, 2010 at 07:36:15
Found it. Thank you. I will give it a go.

Report •

#3
December 7, 2010 at 10:03:12
I'm getting a run time error 424. Object required. Here's what I have:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
' This will calculate every time Event Summary sheet is selected

If Sheet.Name = "EVENT SUMMARY" Then
Outing_Breakdown
End If
End Sub


Report •

Related Solutions

#4
December 7, 2010 at 10:28:58
In the future, should you need to post code in this forum, please read the How To referenced in my signature line. Click on How To.

Try this:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
' This will calculate every time Event Summary sheet is selected
  If Sh.Name = "EVENT SUMMARY" Then
   Outing_Breakdown
  End If
End Sub

You need to use Sh.Name since Sh is declared as the Object.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#5
December 7, 2010 at 21:06:50
works... thank you

Report •

Ask Question