Code to get the Active worksheet on change of worksheets
 

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.

Code to get the Active worksheet on change of worksheets
 

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

" wrote:

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.

Code to get the Active worksheet on change of worksheets
 

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

On Apr 25, 12:02*pm, Mike H wrote:
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



" wrote:
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.- Hide quoted text -

- Show quoted text -