Hi all,

I have written a function which displays a current sheet name in a cell. It
is very simple and it works fine.

However, I have a question. Suppose the function is called "SheetName" and
the current sheet is named "Sheet1".

I put "=SheetName()" in a cell and it properly display "Sheet1". However, if
I change the sheet name, then the function won't automatically do the
update. Any way to do the automation? Thanks a lot.

Frederick Chow
Hong Kong.

Frederick,

You can use built-in functions, which automatically update

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99)

--

HTH

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


"Microsoft Forum" wrote in message
...
Hi all,

I have written a function which displays a current sheet name in a cell.
It
is very simple and it works fine.

However, I have a question. Suppose the function is called "SheetName" and
the current sheet is named "Sheet1".

I put "=SheetName()" in a cell and it properly display "Sheet1". However,
if
I change the sheet name, then the function won't automatically do the
update. Any way to do the automation? Thanks a lot.

Frederick Chow
Hong Kong.

Hi
use a formula for this. See:
http://www.xldynamic.com/source/xld.xlFAQ0002.html

--
Regards
Frank Kabel
Frankfurt, Germany
"Microsoft Forum" schrieb im Newsbeitrag
...
Hi all,

I have written a function which displays a current sheet name in a cell.
It is very simple and it works fine.

However, I have a question. Suppose the function is called "SheetName" and
the current sheet is named "Sheet1".

I put "=SheetName()" in a cell and it properly display "Sheet1". However,
if I change the sheet name, then the function won't automatically do the
update. Any way to do the automation? Thanks a lot.

Frederick Chow
Hong Kong.

I agree that you should just use a formula. However, for future reference,
to update a function, you should use the 'Application.Volatile' Method. See
HELP.
--------------------------
Volatile Method Example

This example marks the user-defined function "My_Func" as volatile. The
function will be recalculated whenever calculation occurs in any cells on the
worksheet on which this function appears.

Function My_Func()
Application.Volatile
'
' Remainder of the function
'
End Function
--------------------------

"Microsoft Forum" wrote:

Hi all,

I have written a function which displays a current sheet name in a cell. It
is very simple and it works fine.

However, I have a question. Suppose the function is called "SheetName" and
the current sheet is named "Sheet1".

I put "=SheetName()" in a cell and it properly display "Sheet1". However, if
I change the sheet name, then the function won't automatically do the
update. Any way to do the automation? Thanks a lot.

Frederick Chow
Hong Kong.

Gary,

Thanks a lot. I tried the method you mentioned last time but somehow it
didn't work. Now somehow it works. Thanks for your assistance.

Frederick Chow.


"Gary Brown" wrote in message
...
I agree that you should just use a formula. However, for future reference,
to update a function, you should use the 'Application.Volatile' Method.
See
HELP.
--------------------------
Volatile Method Example

This example marks the user-defined function "My_Func" as volatile. The
function will be recalculated whenever calculation occurs in any cells on
the
worksheet on which this function appears.

Function My_Func()
Application.Volatile
'
' Remainder of the function
'
End Function
--------------------------

"Microsoft Forum" wrote:

Hi all,

I have written a function which displays a current sheet name in a cell.
It
is very simple and it works fine.

However, I have a question. Suppose the function is called "SheetName"
and
the current sheet is named "Sheet1".

I put "=SheetName()" in a cell and it properly display "Sheet1". However,
if
I change the sheet name, then the function won't automatically do the
update. Any way to do the automation? Thanks a lot.

Frederick Chow
Hong Kong.