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

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

Thanks Tom,

I tried your solution below: SheetNumber = Activesheet.Index

but for some reason it returns 5 with a workbook with 4 worksheets.

Any ideas - the solution is certainly neater and will use it if I can solve
the above.

With regard the application.volatile - how do I make the cell initiate a
calculate event? At the moment the cell function reads like:
=sheetnumber() & " of " & NumberOfSheets()

and it is not recalculating.

Thanks for your time

JC

"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

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

Jack,
You want the index of the WS that the UDF is on ?

Public Function GetMyIndex1(AnyRange As Range) As Long
GetMyIndex = AnyRange.Parent.Index
End Function
'Or
Public Function GetMyIndex2() As Long
GetMyIndex = Application.Caller.Parent.Index
End Function

Add error trapping.

NickHK

"Jack Clift" <(E-Mail Removed)> wrote in message
news:E7600738-90DA-4DE1-97C0-(E-Mail Removed)...
> 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

Thanks alot nick. I implemented the latter (GetMyIndex =
Application.Caller.Parent.Index) and it *sort*of works (well, it works
better).

Problem that I still have is this function is returning 1 more sheet than
actually exists (an no, there are not any hidden sheets - have checked). It
looks something like this:
Sheet 1 returns index 1
Sheet 2 returns index 3
Sheet 3 returns index 4
Sheet 4 returns index 5

Could this be the result of a corrupted workbook (it crashed on me this
afternoon)? If so, any ideas on how to restore?

Any idea would be helpful. Unless it is 'start again'; I have spent hours
getting the formatting right...

"NickHK" wrote:

> Jack,
> You want the index of the WS that the UDF is on ?
>
> Public Function GetMyIndex1(AnyRange As Range) As Long
> GetMyIndex = AnyRange.Parent.Index
> End Function
> 'Or
> Public Function GetMyIndex2() As Long
> GetMyIndex = Application.Caller.Parent.Index
> End Function
>
> Add error trapping.
>
> NickHK
>
> "Jack Clift" <(E-Mail Removed)> wrote in message
> news:E7600738-90DA-4DE1-97C0-(E-Mail Removed)...
> > 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
>
>
>

Jack,
If you are sure there are no hidden sheets, chart sheets or the older
macro/dialog sheets, then I can only think there is something corrupt about
the WS.
Try one of the Code Cleaners available:
http://www.dailydoseofexcel.com/arch...er-version-50/

NickHK

"Jack Clift" <(E-Mail Removed)> wrote in message
news:AB0CD800-DA46-4173-8CD2-(E-Mail Removed)...
> Thanks alot nick. I implemented the latter (GetMyIndex =
> Application.Caller.Parent.Index) and it *sort*of works (well, it works
> better).
>
> Problem that I still have is this function is returning 1 more sheet than
> actually exists (an no, there are not any hidden sheets - have checked).
It
> looks something like this:
> Sheet 1 returns index 1
> Sheet 2 returns index 3
> Sheet 3 returns index 4
> Sheet 4 returns index 5
>
> Could this be the result of a corrupted workbook (it crashed on me this
> afternoon)? If so, any ideas on how to restore?
>
> Any idea would be helpful. Unless it is 'start again'; I have spent
hours
> getting the formatting right...
>
> "NickHK" wrote:
>
> > Jack,
> > You want the index of the WS that the UDF is on ?
> >
> > Public Function GetMyIndex1(AnyRange As Range) As Long
> > GetMyIndex = AnyRange.Parent.Index
> > End Function
> > 'Or
> > Public Function GetMyIndex2() As Long
> > GetMyIndex = Application.Caller.Parent.Index
> > End Function
> >
> > Add error trapping.
> >
> > NickHK
> >
> > "Jack Clift" <(E-Mail Removed)> wrote in message
> > news:E7600738-90DA-4DE1-97C0-(E-Mail Removed)...
> > > 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
> >
> >
> >

Nick,

it must have been a corrupted file - manually I shuffled the order and added
new sheets, then deleted and finally reordered them and the code now works as
expected.

It does make me a bit nervous about the robustness of the file in general -
but that is not your problem.

Thanks for the help

Regards

JC

"NickHK" wrote:

> Jack,
> If you are sure there are no hidden sheets, chart sheets or the older
> macro/dialog sheets, then I can only think there is something corrupt about
> the WS.
> Try one of the Code Cleaners available:
> http://www.dailydoseofexcel.com/arch...er-version-50/
>
> NickHK
>
> "Jack Clift" <(E-Mail Removed)> wrote in message
> news:AB0CD800-DA46-4173-8CD2-(E-Mail Removed)...
> > Thanks alot nick. I implemented the latter (GetMyIndex =
> > Application.Caller.Parent.Index) and it *sort*of works (well, it works
> > better).
> >
> > Problem that I still have is this function is returning 1 more sheet than
> > actually exists (an no, there are not any hidden sheets - have checked).
> It
> > looks something like this:
> > Sheet 1 returns index 1
> > Sheet 2 returns index 3
> > Sheet 3 returns index 4
> > Sheet 4 returns index 5
> >
> > Could this be the result of a corrupted workbook (it crashed on me this
> > afternoon)? If so, any ideas on how to restore?
> >
> > Any idea would be helpful. Unless it is 'start again'; I have spent
> hours
> > getting the formatting right...
> >
> > "NickHK" wrote:
> >
> > > Jack,
> > > You want the index of the WS that the UDF is on ?
> > >
> > > Public Function GetMyIndex1(AnyRange As Range) As Long
> > > GetMyIndex = AnyRange.Parent.Index
> > > End Function
> > > 'Or
> > > Public Function GetMyIndex2() As Long
> > > GetMyIndex = Application.Caller.Parent.Index
> > > End Function
> > >
> > > Add error trapping.
> > >
> > > NickHK
> > >
> > > "Jack Clift" <(E-Mail Removed)> wrote in message
> > > news:E7600738-90DA-4DE1-97C0-(E-Mail Removed)...
> > > > 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
> > >
> > >
> > >
>
>
>