Disable cell updated when changing file reference in a cell

[DrMundo]

Hello all. Happy Wednesday.

So I have a sheet with many references to various files throughout our network. I also have in place a macro which finds and replaces the file names in the cells every month. This, however, becomes a problem when I am trying to proactively update the cell references and the file which excel is trying to look up does not yet exist. A window pops up which requires me to click 'cancel' for each "find and replace" that the macro attempts, which takes quite a long time. So basically, I need a way to either disable to popup (displayalerts = false has not done the trick for me) and allow the macro to run to completion, or I need a way for excel to temporarily stop caring that the file which it is trying to look up does not exist. The latter sounds more appealing to me, as this macro takes a very long time to run and I think it is because excel is looking up so many different files.

Any help is greatly appreciated.

Thanks
DM

 

[sijpie]

there are two ways of doing this, i think, one is quick & dirty, the other is the proper way:

Quick & Dirty:

On Error resume Next
'your code to open the file and change the cell
On Error goto 0

Proper way:

Dim wbChng as workbook
On Error Resume Next
   Set wbChng = Workbooks.Open("YourXL.XLS")
On error Goto 0
   If not wbChng is Nothing Then
      'Your code to change the cells
   End If