put:
Application.Volatile
as the second line in your function. Marks a user-defined function as
volatile. A volatile function must be recalculated whenever calculation
occurs in any cells on the worksheet. A nonvolatile function is recalculated
only when the input variables change. This method has no effect if it's not
inside a user-defined function used to calculate a worksheet cell.




--
Gary''s Student
gsnu200712


"Jack Clift" wrote:

This problem is in two parts (excel 2003);
1. I have written a small macro that calculates the number of sheets in a
workbook (writen in a macro module):
Function NumberOfSheets() As Integer

NumberOfSheets = ActiveWorkbook.Worksheets.Count

End Function

and am using this function in a cell a worksheet using the following notation:
"=NumberOfSheets()"

issue is that the worksheet will not 'recall' the function unless I select
and modify the cell (or the like). How do I make this function update
dynamically per all other cell functions - or at least if a sheets is deleted
or created?

2. Similar to the above I am wanting to write a macro than enumerates each
sheet in order as they are presented in the workbook:
Function SheetNumber() As Integer
Dim WS As Worksheet
Dim i As Integer

Set WS = ActiveSheet

For i = 1 To NumberOfSheets
If WS.Name = ActiveWorkbook.Worksheets(i).Name Then
SheetNumber = i
Exit Function
End If
Next i

SheetNumber = -1
End Function

The 'for next' loop seems a pretty clumsy way to do this, but I can't think
of a better alternative. Any ideas.

This function also needs to dynamically refresh so am hoping solution above
is applicable to this.

Thanks

Jack Clift