Extracting worksheet index

I am trying to write a UDF that extract the worksheet index of the cell that
contains the UDF , similar to ThisCell or Thisworkbook. Any suggestions?

How about

Function thisSheet()
thisSheet = ActiveSheet.Index
End Function

Rowan

"Ziv" wrote:

> I am trying to write a UDF that extract the worksheet index of the cell that
> contains the UDF , similar to ThisCell or Thisworkbook. Any suggestions?

Actually scrap that. Doesn't work when the sheet focus changes.

"Rowan" wrote:

> How about
>
> Function thisSheet()
> thisSheet = ActiveSheet.Index
> End Function
>
> Rowan
>
> "Ziv" wrote:
>
> > I am trying to write a UDF that extract the worksheet index of the cell that
> > contains the UDF , similar to ThisCell or Thisworkbook. Any suggestions?

This line should do what the OP requests:

SheetNumber = Application.Caller.Parent.Index

On Wed, 23 Mar 2005 17:35:03 -0800, "Rowan" <Rowan@discussions.microsoft.com>
wrote:

>How about
>
>Function thisSheet()
> thisSheet = ActiveSheet.Index
>End Function
>
>Rowan
>
>"Ziv" wrote:
>
>> I am trying to write a UDF that extract the worksheet index of the cell
that
>> contains the UDF , similar to ThisCell or Thisworkbook. Any suggestions?

You need to be aware that adding/deleting a new does not trigger a recalc,
so the index doesn't get automatically updated.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Myrna Larson" <anonymous@discussions.microsoft.com> wrote in message
news:7074419v5j8hhqqiqum0grlhurre3pf0c5@4ax.com...
> This line should do what the OP requests:
>
> SheetNumber = Application.Caller.Parent.Index
>
> On Wed, 23 Mar 2005 17:35:03 -0800, "Rowan"
<Rowan@discussions.microsoft.com>
> wrote:
>
> >How about
> >
> >Function thisSheet()
> > thisSheet = ActiveSheet.Index
> >End Function
> >
> >Rowan
> >
> >"Ziv" wrote:
> >
> >> I am trying to write a UDF that extract the worksheet index of the cell
> that
> >> contains the UDF , similar to ThisCell or Thisworkbook. Any
suggestions?
>