Tracking changes with a prompt every time a sheet is saved with vba

I have a sheet I am looking to track changes on. So far I have a tracking method that works just fine, it will log specific information in a table, with one row per save. I am trying to add a Prompt though, so every time the user presses the save button, a form comes up with a textbox and a save/cancel button. The user has to enter something into the textbox if he/she wishes to save. This will stop people from continously saving the file without making changes, and also help track when which changes were made. The sheet is hidden from all users.

I cannot upload the spreadsheet unfortunately, so all you will have to work with is some code and a screenshot!

Excuse my sloppy coding please! My only training in VBA is what I have picked up here so far (Thanks, excelforum! )

Example of the form I am going to be using:
saveas.JPG

The form has the following code in it:

Please Login or Register  to view this content.

The module has the following:

Please Login or Register  to view this content.

In short, it doesn't work. It saves whether I push save or cancel, and does not log the data with either one...

Thanks for looking!

I haven't spent the time to walk through every line of code but I saw an error right away so let's start with that.

The variables ASave and BSave declared in your Module are local to the Module and are not seen by the code in the Form. The variables ASave and BSave in the form are not declared so VBA assumes they are of type Variant and local to the form. Therefore they are always False, and your Sub always exits immediately and allows Excel to continue with the save.

I strongly suggest that you go into the VBA window, click Tools, Options, Editor and click Require Variable Declarations. This will automatically insert the line

Please Login or Register  to view this content.

in every new module. This will cause an error if you try to use an undeclared variable. You would get an error with this code, showing that you have a problem.

To fix this, in the Module change the declarations to look like

Please Login or Register  to view this content.

Also

Please Login or Register  to view this content.

Please Login or Register  to view this content.

This appears correct. Setting Cancel to True will cancel the completion of the event, in this case a save. If you allow it to be False, then Excel will continue with the save after the Sub has exited.

I did what you suggested, and found a really blatant problem here too:

Please Login or Register  to view this content.

Pretty much shows the form then just exits. so I removed the exit sub in there and now it is tracking.

Now it tracks data when save or cancel is pushed, and does not save either way.. hmm

I can ActiveWorkbook.Save to make sure it actually saves I think, but how do I get the cancel to actually cancel the save?

Also I would like it to be able to cancel if the form is just closed instead of the button press