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

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

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

Change

Set mySheet = Activesheet

to

Set mySheet = Application.Caller.Parent

--
HTH

Bob Phillips

"Darren Hill" 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

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

Thanks, that does the trick perfectly. Thanks again :)

Darren

On Sun, 8 May 2005 11:54:55 +0100, Bob Phillips
wrote:

Change

Set mySheet = Activesheet

to

Set mySheet = Application.Caller.Parent




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