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

J

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!
 
D

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
 
C

Clif McIrvin

jayray said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top