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

--
HTH

Bob Phillips

"Darren Hill" <darren@cybersphere.plus.net> wrote in message
news:opsqgbgld6ed89cl@omega...
> 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

[Darren Hill]

Thanks, that does the trick perfectly. Thanks again

Darren

On Sun, 8 May 2005 11:54:55 +0100, Bob Phillips <phillips@tiscali.co.uk>
wrote:

> Change
>
> Set mySheet = Activesheet
>
> to
>
> Set mySheet = Application.Caller.Parent
>



--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/