How to get a MsgBox to pop up if I have VBA write a wrong link

[jayray]

I have a macro that writes a formula in the worksheet that links to
another worksheet. So the VBA code looks something like this "='C:
\PROJECTS\[OtherWorkbook]Sheet1'!A1". The macro is working, but if
somehow I define the address wrong, Excel then pops up the Explorer
form asking me to find the "correct" file. Is there an error trapping
code that I can use in my macro to stop the macro BEFORE the Explorer
form comes up, so that I can have my own MsgBox pop up and then end
the macro run?

Many thanks!

 

[Don Guillett Excel MVP]

I have a macro that writes a formula in the worksheet that links to
another worksheet. So the VBA code looks something like this "='C:
\PROJECTS\[OtherWorkbook]Sheet1'!A1".  The macro is working, but if
somehow I define the address wrong, Excel then pops up the Explorer
form asking me to find the "correct" file. Is there an error trapping
code that I can use in my macro to stop the macro BEFORE the Explorer
form comes up, so that I can have my own MsgBox pop up and then end
the macro run?

Many thanks!

As ALWAYS, post YOUR code for comments

 

[Clif McIrvin]

I have a macro that writes a formula in the worksheet that links to
another worksheet. So the VBA code looks something like this "='C:
\PROJECTS\[OtherWorkbook]Sheet1'!A1". The macro is working, but if
somehow I define the address wrong, Excel then pops up the Explorer
form asking me to find the "correct" file. Is there an error trapping
code that I can use in my macro to stop the macro BEFORE the Explorer
form comes up, so that I can have my own MsgBox pop up and then end
the macro run?

Many thanks!

Are you familiar with the On Error statement? In the macro editor (VBE)
window, type "on error statement" in the help search bar.

Good luck!

Clif