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
|