If Macro finds no external link supress Update Value Prompt

[dieseldogpi]

I have a macro that is updating formulas in a rather large spreadsheet. It updates the formulas every month to pull from a different raw data file with similar names, only the month has changed. The issue is, if the user puts in a name wrong, I get a prompt to find the file for every value in the spreadsheet. It takes forever to click cancel on all of them just to re-do the macro. Any way around this? Thanks in advance.

[Andrew-R]

How does the user enter the name? Is it an InputBox or a cell on the sheet?

[dieseldogpi]

Yes, the data is entered through an input box. I'm just worried if the user enters in the wrong month, or even miss-spells the month, that they will have to click cancel 350 times in a row.

[dieseldogpi]

Nobody with help on this one?

[Andrew-R]

Without seeing your code it's very hard to know exactly what you're doing, but maybe something along the lines of the below...

Please Login or Register  to view this content.

[dieseldogpi]

Sorry, I guess what I have in there could have helped.

Please Login or Register  to view this content.

The macro itself is just searching through formulas to change the year and month in the formula so the spreadsheet referenced will change. Now if the user unputs anything incorrectly, for each formula, a box to search for the named spreadsheet comes up. Some of the files I want to use this on have 100's of instances, so this would get very irritating. Thanks again for the help.

[romperstomper]

Try using:

Please Login or Register  to view this content.

before the Replace lines and set back to True again afterwards.

Note: when posting code on the forum, you must use code tags. I have added them for you on this occasion, but please make sure you use them in future.

[dieseldogpi]

That is exactly what I needed. Works perfectly...thanks. I will use the code tags from here on out as well. Thanks again!

[romperstomper]

Much obliged.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save