Return number of current sheet

B

Brandon

I'm looking for a way to create a function to return the current sheet you’re
working in. Not it’s name (I’ve found that function), but the number of the
sheet. Example: if you have a total of 8 sheets, and you’re working on the
3rd sheet, I’d like to return the number 3 in a certain cell on the third
sheet, 4 on the fourth sheet, 5 on the fifth sheet, etc., and have those
numbers update if sheets are added or deleted. Is that possible?
 
J

JE McGimpsey

One way:

Public Function SheetNum(Optional rng As Range) As Variant
Application.Volatile
If rng Is Nothing Then
If TypeName(Application.Caller) = "Range" Then
Set rng = Application.Caller.Cells
Else
SheetNum = CVErr(xlErrRef)
Exit Function
End If
End If
SheetNum = rng.Parent.Index
End Function

usage:

=SheetNum()

or

=SheetNum(MySheet!A1)


note that this will not automatically update when a sheet is moved
within a workbook - Application.Volatile will cause it to update the
next time a calculation is made.
 
B

Brandon

Thanks very much. That worked just fine!

JE McGimpsey said:
One way:

Public Function SheetNum(Optional rng As Range) As Variant
Application.Volatile
If rng Is Nothing Then
If TypeName(Application.Caller) = "Range" Then
Set rng = Application.Caller.Cells
Else
SheetNum = CVErr(xlErrRef)
Exit Function
End If
End If
SheetNum = rng.Parent.Index
End Function

usage:

=SheetNum()

or

=SheetNum(MySheet!A1)


note that this will not automatically update when a sheet is moved
within a workbook - Application.Volatile will cause it to update the
next time a calculation is made.
Click to expand...
 
H

Harlan Grove

JE McGimpsey said:
If TypeName(Application.Caller) = "Range" Then
Set rng = Application.Caller.Cells
Click to expand...
....

Why TypeName(x) = "y" rather than TypeOf x Is y? Also, when the condition is
true, won't Application.Caller.Parent resolve to the same worksheet as
Application.Caller.Cells.Parent? If so, why use .Cells?
 
Top