Macro works but crashes Excel 2010
This set of macros does everything they're supposed to do: stop the user from saving if they haven't entered the basic info in a few cells, create a folder and save the file there under a name from a cell, run spellcheck, and timestamp the footer with the user and time saved.
The only problem is that it crashes Excel every time it's used and Excel has to run auto-recover to reopen the file. Can anyone please help me figure out what I'm doing wrong?
Please Login or Register to view this content.
I would suggest that you put a Debug.Print between each line of code and display the pieces of data that are being used. That way, you'll know how far it gets and have some information to look at.
Sorry I can't be more specific.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
It always crashes creating the new folder, then it's usually alright but sometimes it saves the file twice in a row on resaving; first I get the message "...do you want to overwrite the file?" and when I select "yes", the save as dialogue pops up.
Last edited by Willardio; 10-07-2012 at 08:18 PM.
TMShucks has provided the correct answer.
Last edited by Trebor76; 10-07-2012 at 08:27 PM.
____________________________________________
Please ensure you mark your thread as Solved once it is. Click here to see how
If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post
You're in the Before_Save event and you're saving the workbook. You need to disable events before trying to save and then re-enable afterwards.
Regards, TMS
thanks for the suggestion trebor. Same error, still crashes. but it does only save one way on the re-save, so that's an improvement.
Thank you TMShucks, I have a macro for that, still getting the same error.
this is the macro I use to save without running the event, I got it in another thread:
Please Login or Register to view this content.
did i "Dim" something wrong?
Maybe you should post a sample workbook. We're working a bit in the dark here.
Here's the sample (second try)
Sorry, there was nothing in the cell to "save as" in that previous file, but this one is right:
save event sample 2.xlsm
And thanks again for taking a look.
-Willardio
Last edited by Willardio; 10-07-2012 at 09:59 PM.
putting a save event in a beforesave_event creates an infinite loop that's guaranteed to crash the program, unless you disable events in a line prior to the save event, then re-enable events in the line after, in which case it saves twice.
The code below accomplishes the important part of what I set out to do, which is run spellcheck on a protected sheet and prevent users from leaving certain cells blank before saving. I will try to figure out how to use getSaveasName to extract the save name from a cell, but for now this is good enough. The accompanying BeforeClose event is necessary to keep the program from freezing at close. Application.Events will re-enable automatically next time you open the file.
Please Login or Register to view this content.
Last edited by Willardio; 10-10-2012 at 12:14 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks