I have been using the following code in VBA with the PriorSheet function in a
cell successfully in Excel 2003. However, it is bombing in Excel 2007
(Vista). Anyone have an update that will work in 2007? Thanks!

Function PriorSheet(PriorCell As Variant) As Variant
Application.Volatile
PriorSheet = Application.Caller.Parent.Previous.Range(PriorCell .Address)
End Function

try declaring priorcell as Range

Function PriorSheet(PriorCell As Range) As Variant
Application.Volatile
PriorSheet = Application.Caller.Parent.Previous.Range(PriorCell .Address)
End Function

Make sure the function isn't in a sheet module, but in a general module.

--
Regards,
Tom Ogilvy


"David" wrote:

I have been using the following code in VBA with the PriorSheet function in a
cell successfully in Excel 2003. However, it is bombing in Excel 2007
(Vista). Anyone have an update that will work in 2007? Thanks!

Function PriorSheet(PriorCell As Variant) As Variant
Application.Volatile
PriorSheet = Application.Caller.Parent.Previous.Range(PriorCell .Address)
End Function