I'm looking for a way to create a function to return the current sheet youre
working in. Not its name (Ive found that function), but the number of the
sheet. Example: if you have a total of 8 sheets, and youre working on the
3rd sheet, Id 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?

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.


In article ,
"Brandon" wrote:

I'm looking for a way to create a function to return the current sheet youre
working in. Not its name (Ive found that function), but the number of the
sheet. Example: if you have a total of 8 sheets, and youre working on the
3rd sheet, Id 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?

Thanks very much. That worked just fine!

"JE McGimpsey" wrote:

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.


In article ,
"Brandon" wrote:

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?

"JE McGimpsey" wrote...
....
If TypeName(Application.Caller) = "Range" Then
Set rng = Application.Caller.Cells
....

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?