if data file is opened as read only, then the macro gives an "error" message

Hello!

I am new to VBA and have been learning from the web for the past few days, so far I have been doing with google as my teacher, but today I got a little stuck so I would like some help. I apologize if I am missing details or if my code is too novice.

I have a template where different users will be inputting data, then that data is being transferred to another "data" file. The problem I have is that at some point of the day the "data" files will be opened to be serviced (move/delete data.) I want the user to have a smooth experience with the template and not experience any runtime/VBA errors of any kind. My problem is the following: after the data is entered and the user clicks the "submit" button, the data is exported to the data files and they get a message saying "your request has been submitted. Thank you." Til then so far so good, but in case the data file is open by someone then excel will open it as read only, I want the code to error somehow and the user gets a message that says "Our data files are currently being updated. Please try again later."

I first tried "On error resume next," but since I have Displayalerts as False, Excel is not really erroring but instead going with the defaults. When the data file is opened as "read only" what excel does is save new copy of the file one folder up to the one where the file is originally at (if that even made sense) and it never errors, so no luck with this option.

Please Login or Register  to view this content.

this code I got it from someone else, but the goal is somewhat different from what I need. If this code errors, then the code prompts the user to "save as." Thought I could use this as a base and modify to what I need, but so far no luck

Please Login or Register  to view this content.

any help is greatly appreciated thanks so much!!

See this thread http://www.excelforum.com/excel-prog...book-open.html

Alter the Msgbox to display the messages you want. (You don't need to return the other users name in the Msgbox if you don't want to - but it can be handy! Particularly if you want to PM them to close the file they have open! )

If your thread is solved, please use 'Thread Tools' to mark it SOLVED as a courtesy to other forum users.

If my posts helped solve your thread, please consider taking a few seconds to click '* Add Reputation' on the post that helped. Thank you.