BeforeSave Userform, Cancel = don't save, OK = save

Not tested, but:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CancelButton_Click()
        MsgBox "If Comment History is not added, no changes will be saved.", vbCritical + vbOKOnly, "Cancel Comment"
        Unload Me
        <SPAN style="color:#007F00">'  Close without saving changes</SPAN>
        ActiveWorkbook.Close <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> OkButton_Click()
    <SPAN style="color:#00007F">Set</SPAN> MySheet = Sheets("Update History")
        NextCell = Sheets("Update History").Range("a65536").End(xlUp).Row + 1
        
        <SPAN style="color:#00007F">With</SPAN> MySheet
            .Unprotect "dstengineering"
                .Cells(NextCell, 1) = Environ("username")
                .Cells(NextCell, 2).Value = Now
                .Cells(NextCell, 3) = txtComments.Value
            .Protect "dstengineering"
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        
        Unload Me
        <SPAN style="color:#007F00">'   Close & Save Changes</SPAN>
        ActiveWorkbook.Close <SPAN style="color:#00007F">True</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

And take Cancel = True out of the BeforeSave event. That's overriding any attempt to save.

Hope that helps,

Smitty

Thanks Smitty,

That kinda works, if its like this:
So that it exits from the userform. The problem with this is, that it closes the document regardless now. If save button or Save as is used, then the document should stay open after Userform runs, regardless of Cancel or OK button click. Only should exit the document if the Spreadsheet is closed and tries to save changes before closing.

The problem with this is, that it closes the document regardless now. If save button or Save as is used, then the document should stay open after Userform runs, regardless of Cancel or OK button click. Only should exit the document if the Spreadsheet is closed and tries to save changes before closing.

Click to expand...

To keep the wb from from closing, take out the ActiveWorkbook.Close statements. The True/False in the statements tells Excel whether to save or not. You can replace it with ActiveWorkbook.Save in the OK click event.

If a user selects Save or Save As, as long as you took Cancel = True out of the BeforeSave event, it should do what you want there.

I'm not really clear on the last sentence.

Smitty

I'm not doing a very good job of explaining.

This code works almost exactly how it should, only problem is that the Userform run from someone trying to save document. If the user tries to save the document, then hits the Cancel button on this form, then i want the document to not save either.

Wrokbook:

Userform:

I really just need something to add to the cancel button code that will stop the workbook from saving and exit the Userform. My problem with ActiveWorkbook.Close False is that this closes the workbook, even if only the save button started the macro and all changes are lost.