Auto refresh a macro?

[USCguy09]

i found a macro where it counts the # of files in a specified directory.
Problem is, when I add/delete files in the directories, the true # isn't reflected in excel unless I double click & enter on the cell containing the formula.
I recorded a macro, titled refresh, that does this for cells D19 - D23.

Is there a way where I can get the refresh macro to just run every X minutes instead of me having to click it every time?

Sub refresh_files()
'
' refresh_files Macro
'

'
    Range("D19").Select
    ActiveCell.FormulaR1C1 = "=countfilesif(R[-8]C,R[-8]C[3],FALSE,R[-8]C[5])"
    Range("D20").Select
    ActiveCell.FormulaR1C1 = "=countfilesif(R[-8]C,R[-8]C[3],FALSE,R[-8]C[5])"
    Range("D21").Select
    ActiveCell.FormulaR1C1 = "=countfilesif(R[-8]C,R[-8]C[3],FALSE,R[-8]C[5])"
    Range("D22").Select
    ActiveCell.FormulaR1C1 = "=countfilesif(R[-8]C,R[-8]C[3],FALSE,R[-8]C[5])"
    Range("D23").Select
    ActiveCell.FormulaR1C1 = "=countfilesif(R[-8]C,R[-8]C[3],FALSE,R[-8]C[5])"
    Range("D19").Select
End Sub

 

[Andrew Poulsom]

If you make your countfilesif function volatile it will refresh each time the workbook is calculated. To do that put:

Application.Volatile

at the beginning of the function.

 

[USCguy09]

If you make your countfilesif function volatile it will refresh each time the workbook is calculated. To do that put:

Application.Volatile

at the beginning of the function.

thanks but that only refreshes when i manually recalculate it.
i looked into this further and found the Ontime method and Sub Auto_open() function.

seems to be working like how i want it now.

Sub Auto_Open()
Call auto_refresh
End Sub

Sub auto_refresh()

' execute macros
Range("D19").Select
    ActiveCell.FormulaR1C1 = "=countfilesif(R[-8]C,R[-8]C[3],FALSE,R[-8]C[5])"
    Range("D20").Select
    ActiveCell.FormulaR1C1 = "=countfilesif(R[-8]C,R[-8]C[3],FALSE,R[-8]C[5])"
    Range("D21").Select
    ActiveCell.FormulaR1C1 = "=countfilesif(R[-8]C,R[-8]C[3],FALSE,R[-8]C[5])"
    Range("D22").Select
    ActiveCell.FormulaR1C1 = "=countfilesif(R[-8]C,R[-8]C[3],FALSE,R[-8]C[5])"
    Range("D23").Select
    ActiveCell.FormulaR1C1 = "=countfilesif(R[-8]C,R[-8]C[3],FALSE,R[-8]C[5])"
    Range("D19").Select
' submit macro to run again in 1 min
Application.OnTime Now + TimeValue("00:01:00"), "auto_refresh"

End Sub