Custom Cell Functions
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 |
All times are GMT +1. The time now is 12:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com