Corrupt VBA file on save

[Nour]

I have this macro application for POS and clinic register. However I just made some addition to the macro to increase the functionality but once it opens the first time, it refuses to open the second time and says repairing file and exit excel. My macros have no errors. In fact even the macros are not even called but the user form refuses to initialize. I need help. I have even attempted increasing the modules to 5 from 3 and but nothing works.

 

[rlv01]

'Repairing the file' messages are never good. At that point I usually begin to think in terms of salvaging the corrupted workbook by moving everything to a new workbook. The general method looks like this:

1. Create a new folder somewhere.
2. Using the Visual Basic Editor (VBE), for each code module, user form, worksheet, class module - right click and export the file to the new folder. Don't forget the workbook code module.
3. Save the damaged/corrupt macro enabled .xlsm workbook to a new .xlsx file. That will strip out all the macros.
4. Close the new .xlsx file.
5. Re-open the new .xlsx file.
6. Using the Visual Basic Editor (VBE), import each file (from the new folder) that you previously exported.
7. Save the .xlsx file as a new macro-enabled (.xlsm) workbook.
8. Test the new workbook to be sure everyhing is working.

If something goes wrong, you still have your original file so this is a low-risk procedure.

 

[Logit]

Create a copy of your existing workbook so no matter what changes you make in the future while attempting to repair the error/s, you aren't adding more errors to the only existing copy you have.

With the copy ... you have to determine why the UserForm is refusing to open. My experience in this regard indicates there is a specific error in code somewhere in the UserForm only that needs correction.

 

[Nour]

Well I have many backups. The issue is if I am in the vba editor, debugging shows no errors. Starting the userform. Within vba works very fine. In fact I never knew. Of this problem until after exiting vba. The first startup opens ok but close the userform and the next attempt to open is, it goes to 100%, then takes a little while and then shows attempting to repair and closes. I have checked my code 100x and I don't see any error. I have been using this program for over 1year now and it's stable. All I added now we're few text boxes and command buttons with their respective codes in a new module which has to be clicked for it to work. However I also notice if I deactivated macro, open vba and make any trivial change to the code, it opens and works well for the first opening as usual. In fact removing or deactivating the added code does not stop the corruption. I am confused.

 

[Logit]

Have you tried to "Open and Repair" a copy of your workbook ?

 

[rlv01]

Well I have many backups. The issue is if I am in the vba editor, debugging shows no errors. Starting the userform. Within vba works very fine. In fact I never knew. Of this problem until after exiting vba. The first startup opens ok but close the userform and the next attempt to open is, it goes to 100%, then takes a little while and then shows attempting to repair and closes. I have checked my code 100x and I don't see any error. I have been using this program for over 1year now and it's stable. All I added now we're few text boxes and command buttons with their respective codes in a new module which has to be clicked for it to work. However I also notice if I deactivated macro, open vba and make any trivial change to the code, it opens and works well for the first opening as usual. In fact removing or deactivating the added code does not stop the corruption. I am confused.

It sounds to me like the workbook became corrupted. It happens. That may have nothing to do with your VBA changes. Since you say you have backups, you should revert to the most recent.

Also, if this is a workbook you have been working on for a long time, have you ever cleaned your code? Excel builds up 'garbage' (for lack of a better word) over time when you make VBA changes, and in my opinon eventually causes weird issues with a workbook.

If you don't clean your files periodically you will begin to experience strange problems caused by this extra baggage.

Cleaning means exporting all the VBComponents in your project to text files (not unlike what I listed in my post above), deleting the modules in the project and then importing the components back from the text files. It can be done manually, but there are a few utilities that make the process a lot easier. I use MZTools, but there are others.

 

[Nour]

What I did actually was to open a previous backup which has been working without the new updated codes. Copy each module into a text file and paste it later into the old file to update the code. Yet in the vba environment, the userform always works flawlessly. But once I exit the userform (which has thisworkbook.Save and userform. Quit) it will not load again. Will load to 100% then repair and quit the excel splash screen. Also I noticed it tendstends to significantly reduce the size of the. Xlsb file. If I disable the workbook save command, and quit without saving, the userform will open fine. This makes me think the issue is during saving the workbook at quitting. Have no idea why. Pls if anyone wants to assist, I can send the file to him to see. Thanks for the help.

 

[Nour]

Have you tried to "Open and Repair" a copy of your workbook ?


View attachment 99871

Yes. It won't open. But once I disable macro, open the vba editor and do anything and save again then enable macro. It will always open the first time. My guess is it gets corrupted while saving on exit. Also if I disable the thisworkbook. Save and quit the userform, it does not get corrupted.

 

[Logit]

Are you willing to post the workbook for download without including any private information ?

 

[Nour]

Are you willing to post the workbook for download without including any private information ?

Yes. I am. All I want is a solution. I can erase the data before posting.