You are correct. Just throwing out ideas.
--

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


"Jim Thomlinson" wrote:

This code is tied to the active sheet. What happens when you put this
function into sheet 1 but run a calculation from Sheet 2. It will look at the
wrong sheet I suspect.

The answer given will depend on what sheet is active the last time a calc
was run. That could result in some very unpredictable behavior.
--
HTH...

Jim Thomlinson


"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/


"w_c_mead" wrote:

It might be useful to back up and pose the problem I'm trying to solve. I'd
like to be able to use the sheet number (index) of a sheet as a function
argument so that the function can refer to cells using a
Sheets(nsheet).Cells(i,j).Value construct. This seems to work if I specify
nsheet explicitly. But that is not general enough to survive if a new sheet
is added to the workbook.

I can't find a property or method that allows me to set nsheet to the
current sheet number so that I could offset it to refer to the previous or
next sheet.

I've got a kluge that works if I store the sheet number in a cell on each
sheet, but this value has to be reset by a "renumber" operation if a sheet is
inserted. Yuk!

How would you go about this?