Code to get the Active worksheet on change of worksheets

[dannic06]

I am having some difficulty here. It has been some time since I have
done any programming in VB for Excel.

I need to get the name of the worksheet and perform a function if it
is changed to that worksheet.

Basicly I have one worksheet with a column with numerical values and I
need to recalculate another sheet to count the number of occurances
within the first sheet when the first is changed or the sheet is
changed. Which ever is easier.

I do not have a set number of fields so I know I will have to loop
through the column of the first sheet. That's not a problem. The
problem is just starting it off.

I have tried this:

Public MyWorkbook As Workbook

Private Sub MyWorkbook_SheetChange(ByVal Sh As Object, _
ByVal Source As Range)


If Sh.Name = "Totals" Then
Msg "testing"
End If


End Sub

but it doesn't seem to do anything for me when I change sheets.

Help would be greatly appreciated.

 

[Mike H]

Hi,

Use a different event. use the 'Activate' event from the sheet you want to
work on and then run your code

Private Sub Worksheet_Activate()
MsgBox ActiveSheet.Name & " is active"
'do something
End Sub

Mike

 

[Dannic]

Thanks, that seems to have helped. Now maybe I can get the rest of
it.