Counter sums quantity of files in said folder but doesnt update unless double clicked

[BigError]

Option Compare Text
Option Explicit
 
Function CountFiles(Directory As String) As Double
     'Function purpose:  To count all files in a directory
    Dim fso As Object, _
    objFiles As Object
     
     'Create objects to get a count of files in the directory
    Set fso = CreateObject("Scripting.FileSystemObject")
    On Error Resume Next
    Set objFiles = fso.GetFolder(Directory).Files
    If Err.Number <> 0 Then
        CountFiles = 0
    Else
        CountFiles = objFiles.Count
    End If
    On Error GoTo 0
End Function

Sub TestCount()
     'To demonstrate the use of the CountFiles function
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    If fso.folderexists(Selection) Then
        MsgBox "I found " & CountFiles(Selection) & " files in " & Selection, _
        vbOKOnly + vbInformation, CountFiles(Selection) & " files found!"
    Else
        MsgBox "Sorry, but I can't find the folder:  " & vbCrLf & Selection _
        & vbCrLf & "Please select a cell that has a valid" & vbCrLf & _
        "folder name in it!", vbOKOnly + vbCritical, "Error!"
    End If
     
End Sub

How do I get this to refresh or auto update?

Also I have no Idea what I'm doing as this script was pinched from another user of this fine website and community!

[paulked]

Hi BigError! Welcome to the forum.

When do you want it to update/refresh?

[SamT]

Many ways. One way is to use "CountFiles" as an Equation Function in a cell

=CountFiles("C:\MyFolder")

Alternatively, use any WorkSheet or Workbook Event Sub. (Selection_Change, Sheet_Activate, Workbook_Open, etc)

BTW, I added Code Formatting Tags to you post. Please use the # icon when adding Code blocks to a post,

[BigError]

Thank you for the recommendation and editing of the post. Looks much cleaner!

[BigError]

Hi BigError! Welcome to the forum.

When do you want it to update/refresh?

Well I'm open to any suggestions, I don't have much experience so I'm more wondering what options I have! Thank you very much for your assistance.

[SamT]

Well I'm open to any suggestions,

Open Excel... Right Click on a worksheet Tab and choose "View Code."

Above the Code page, thee are two DropDowns, General and Declarations.

Drop down the General and select Worksheet. Then...

Drop down the Declarations and look at all the Events that occur on a worksheet. Pick one to be the Trigger of your code.

[BigError]

Many ways. One way is to use "CountFiles" as an Equation Function in a cell

=CountFiles("C:\MyFolder")

Alternatively, use any WorkSheet or Workbook Event Sub. (Selection_Change, Sheet_Activate, Workbook_Open, etc)

BTW, I added Code Formatting Tags to you post. Please use the # icon when adding Code blocks to a post,

I used the equation to originally get the cell to operate as I want it but now its refreshing that information, the issue is it will certainly count the files in said folder but after it counts, it wont update after more files are added to the folder unless double clicked to show the code and exited. How can I get it to update as files are added without having to double click the code as there will be many many cells that need updating as files are being added to many different folders that need to be counted.

Sorry if my jargon is off! I really appreciate the help

[BigError]

Open Excel... Right Click on a worksheet Tab and choose "View Code."

Above the Code page, thee are two DropDowns, General and Declarations.

Drop down the General and select Worksheet. Then...

Drop down the Declarations and look at all the Events that occur on a worksheet. Pick one to be the Trigger of your code.

I followed your instructions and chose

Private Sub Workbook_Open()


End Sub


Private Sub Workbook_SheetTableUpdate(ByVal Sh As Object, ByVal Target As TableObject)


End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


End Sub

It doesnt really do anything though. sorry man... Im super new to this.

[BigError]

*Update*
I'm trying to refresh the number shown from my custom function. it works as I need it but once it calculates and something is added or removed from the folder the cell does not update. I have to find a round about way to get it to update where I would like if it was set on a timer to check, or even update anytime the worksheet is opened.

Writing this again for clarity of the question. I have no preference towards how this happens and am unsure of the options I have as I'm new to the Excel programming community.

[SamT]

Add this line a the top of your UDF (User Defined Function, AKA Custom Function)

Application.Volatile

That tells Excel to calculate it every time the sheet is calculated

something is added or removed from the folder

That action may not touch Excel, so Excel has no way to know. Are you adding-removing files from that folder using Excel? If yes, then add the Range Calculate Method below to the bottom of that sub. Add the sheet name to the Range. Sheet?.Range(?).Calculate

These two, along with the Volatile method above, will trigger it most often

Private Sub Worksheet_Activate() 'Sheet open event
 Range("A1").Calculate 'Edit to refer to the range the UDF is in
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range) '
 Range("A1").Calculate
End Sub

[BigError]

Add this line a the top of your UDF (User Defined Function, AKA Custom Function)

Application.Volatile

That tells Excel to calculate it every time the sheet is calculated

That action may not touch Excel, so Excel has no way to know. Are you adding-removing files from that folder using Excel? If yes, then add the Range Calculate Method below to the bottom of that sub. Add the sheet name to the Range. Sheet?.Range(?).Calculate

These two, along with the Volatile method above, will trigger it most often

Private Sub Worksheet_Activate() 'Sheet open event
 Range("A1").Calculate 'Edit to refer to the range the UDF is in
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range) '
 Range("A1").Calculate
End Sub

Perfect! Thank you very much for your assistance Sam!