Custom Cell Functions
Tom,
Have spent a bit more time on this - neither my nor your solution is going
to work as it relies on the 'activesheet' and hence updates all sheets with
this number, not each sheet to its own.
Any ideas? is there someway to use the 'Me' keyword to differentiate
between which instance is calling the function
"Tom Ogilvy" wrote:
Function NumberOfSheets() As Integer
Application.Volatile
NumberOfSheets = ActiveWorkbook.Worksheets.Count
End Function
if all you have in the workbook are worksheets
Function SheetNumber() As Integer
Application.Volatile
SheetNumber = Activesheet.Index
End function
this falls apart if you have none worksheets in the workbook.
Function SheetNumber() As Integer
Application.Volatile
if sheets.count = worksheets.count then
SheetNumber = Activesheet.Index
else
' your looping method
end if
End function
application.Volatile makes the function volatile. It gets recalculated on
every calculate event that would normally include that cell.
--
Regards,
Tom Ogilvy
"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
|