VBA to use Refresh (F9) command when a certain cell has a #VALUE! error

Posted by Shrek on December 06, 2001 11:54 AM

I've got a cell that has a CELL function in it, returning the filename. (The filename has the date in it.)

Other cells look at that cell for some VLookups, so it's important that this cell be accurate.

However, sometimes, (I don't know why) the cell returning the filename "forgets" the filename, and I must press F9 to refresh the sheet.

Is there code that can automatically refresh the sheet when the cell's value changes to #VALUE!?

Thanks

Posted by Colo on December 07, 2001 1:02 AM

Hi Shrek.
Try following code.
And close your file, reopen it.

Sub Auto_Open()
Application.Calculation = xlAutomatic
End Sub

If it does not work well, please use following code
to make Excel calculate compulsorily.
How to use this code, right click on your "sheet?" tab
and select "View Code", Paste the following code into there.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Calculate
End Sub