Thanks to both Jim and Toby... This works. The volatile might not be needed,
but it does no harm except perhaps to over-calculate the function. The
VeryHidden sheet is an interesting thought, too, and one I'd never run across.

- Bill

"Air_Cooled_Nut" wrote:

What Mike said is true, you can only return a value and NOT affect the
contents of another cell :-( I wish this were possible as I'm sure many have
had this need. Anyway, here is a way to get the sheet info:
--- Code Start ---
Option Explicit

Public Function aatestset(Optional DisplayIndex As Boolean) As Variant
Application.Volatile
If DisplayIndex Then
aatestset = ActiveSheet.Index
Else
aatestset = ActiveSheet.CodeName
End If
End Function
--- Code End ---

Not sure if you want the Application.Volatile but if so, the function will
be executed whenever there's a change event on the sheet (basically, though
probably not the correct verbage) like changing a cell's contents or hitting
the F9 function key (calculate sheet).

Though you may think your method is kludgy, it's probably the only way you
can accomplish it. Have you thought about having a VeryHidden sheet to use
as a reference sheet? That way you can use it for storing information that
the user doesn't neet to see nor allow them the ability to alter it ;-)
Simply create a sheet and name it, then in the VBA Editor select the sheet
and change its Visible property to xlSheetVeryHidden (use the Properties
Window).
--

Toby Erkson
http://excel.icbm.org/