How to get a UDF to ignore all sheets but its own?

[Darren Hill]

I'm having problems with the macro below.
I have several sheets with data formatted the same way.
I have the UDF below in each of these sheets.
When I switch sheets, the function does not update. I then press F9 to
calculate, and the cell in all sheets is updated to include the value for
the active sheet.
I then change sheets, and notice the function contains the data for the
last sheet I was on. So I press F9 again..

I would like the function to report only the values for the sheet it is
on. Can I do that?

Function CountStunts(Optional AssignedOrNot As Boolean = True)
Application.Volatile
Application.EnableEvents = False
Dim mySheet As Worksheet
Set mySheet = ActiveSheet
Dim CountAssigned As Integer, CountUnassigned As Integer, tempStunts As
Integer
Dim mycelladdress As String
Dim myCell As Range, StuntRange As Range
CountStunts = 0: CountAssigned = 0: CountUnassigned = 0

Set StuntRange =
mySheet.Range("$H$20:$H$23,$H$25:$H$27,$H$29:$H$31,$H$33:$H$35,$H$37:$H$39,$H$41:$H$43,$H$45:$H$46,$H$48:$H$49,$H$51:$H$52,$H$54:$H$55,$H$57:$H$58,$H$60:$H$61,$H$63:$H$64,$H$66:$H$67,$H$69,$H$71,$H$73,$H$75")
Set StuntRange = Application.Union(StuntRange,
mySheet.Range("$P$20:$P$23,$P$25:$P$27,$P$29:$P$31,$P$33:$P$35,$P$37:$P$39,$P$41:$P$43,$P$45:$P$46,$P$48:$P$49,$P$51:$P$52,$P$54:$P$55,$P$57:$P$58,$P$60:$P$61,$P$63:$P$64,$P$66:$P$67,$P$69,$P$71,$P$73,$P$75"))

For Each myCell In StuntRange
mycelladdress = myCell.Address
If myCell <> "" Then
tempStunts = Len(myCell.Text)
If myCell.Offset(0, -5).Value <> "" Then
CountAssigned = CountAssigned + tempStunts
Else
CountUnassigned = CountUnassigned + tempStunts
End If
End If

Next myCell
CountStunts = CountUnassigned
If AssignedOrNot Then CountStunts = CountAssigned
Application.EnableEvents = True
End Function


Thanks

Darren

 

[Bob Phillips]

Change

Set mySheet = Activesheet

to

Set mySheet = Application.Caller.Parent

 

[Darren Hill]

Thanks, that does the trick perfectly. Thanks again

Darren