How to cancel save if chosen with BeforeSave?

[postelrich]

I have made a userform that popsup when someone saves the file that asks them to put in some comments and copies that to a changelog in the workbook. Everything works fine except for the cancel button which will still save if chosen. I have tried searching and a bunch of methods. Here is what I have so far.

Code for the event:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 
UserForm1.Show
 
If CancelIt = True Then
    Cancel = True
End If
 
Unload UserForm1
 
End Sub

Code for the Userform:

Public CancelIt As Boolean
 
Private Sub CommandButton1_Click()
 
Dim comments As String
Dim LastRow
comments = TextBox1.Value
 
With Sheets("Changelog & Reference")
     LastRow = .UsedRange.Rows.Count + 1
    .Range("A" & LastRow).Value = Now
    .Range("B" & LastRow).Value = Environ("USERNAME")
    .Range("C" & LastRow).Value = comments
End With
 
Unload UserForm1
End Sub
 
Private Sub CommandButton2_Click()
 
CancelIt = True
UserForm1.Hide
 
End Sub

 

[kpark91]

Try putting

Option Explicit

at the top of your code.

http://msdn.microsoft.com/en-us/library/y9341s4f(v=vs.80).aspx

 

[postelrich]

That didn't help. I added it to the top of both as well as had to declare the CancelIt variable in the ThisWorkbook code.

Option Explicit
Public CancelIt As Boolean
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
UserForm1.Show
If CancelIt = True Then
    Cancel = True
End If
Unload UserForm1
    
End Sub

 

[kpark91]

Just for verification,what do you mean by still saving?
Does it show a SaveAs Dialog? or just saves?

How is the document saved? Is it through VBA or just user-input?

 

[postelrich]

What I have done is when someone hits the save or saveas button in Excel (not VBA) the userform will popup. It has both an OK button and a Cancel button. I want it so when they hit cancel, it doesn't save. But right now when I choose Cancel it will still save (or go to the SaveAs dialog if SaveAs was chosen).

 

[postelrich]

bump...still trying to figure this one out. Does have something to do with passing by value/reference?

 

[postelrich]

bump