Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to make a function in a cell update
I have a worksheet with cells that contain the count of the number of files
in a particular folder as defined by "=countfiles(H3)", where H3 equals the name of the folder I want to count files in. The countfiles function looks like this: Function countfiles(ByVal foldername As String) With Application.FileSearch .LookIn = "S:\Security" & foldername .FileType = msoFileTypeWordDocuments .Execute countfiles = .FoundFiles.Count End With End Function It works just fine, but the problem comes in when the count in one of those folder is changed in some way...The cell with the formula in it that refers to the folder where the count changed doesn't update to reflect the new count. How can I get these functions to "refresh" or "recalculate"? I've tried closing and reopening the spreadsheet, but that doesn't work. I've tried going to Data---!Refresh Data, but that option is grayed out. Any ideas? -Brian |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to make a function in a cell update
You can add:
application.volatile to the top of your routine--and your function will recalculate when excel recalculates. I don't know if this will be a problem for you. If you use that function lots of times, it could slow down the calculation. And if something changes in that folder and you don't do anything in excel that causes it to recalc, then the results of this function won't be up to date. Brian Beck wrote: I have a worksheet with cells that contain the count of the number of files in a particular folder as defined by "=countfiles(H3)", where H3 equals the name of the folder I want to count files in. The countfiles function looks like this: Function countfiles(ByVal foldername As String) With Application.FileSearch .LookIn = "S:\Security" & foldername .FileType = msoFileTypeWordDocuments .Execute countfiles = .FoundFiles.Count End With End Function It works just fine, but the problem comes in when the count in one of those folder is changed in some way...The cell with the formula in it that refers to the folder where the count changed doesn't update to reflect the new count. How can I get these functions to "refresh" or "recalculate"? I've tried closing and reopening the spreadsheet, but that doesn't work. I've tried going to Data---!Refresh Data, but that option is grayed out. Any ideas? -Brian -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
How to make IF function not change cell value? | Excel Worksheet Functions | |||
How to Enter data and Function in same cell | Excel Discussion (Misc queries) | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
Function syntax to compare cell contents | Excel Worksheet Functions |