I'm 90% done with an Excel 2003 addin I've created to make my team's
documents compliant with corporate document control requirements, which
involves a couple of forms. I am not a pro at this, so my code may not look
pretty, but it works ... sort of.

I'm having issues with my BeforeSave event. I want to draw a distinction
between when a user selects Save or SaveAs, as they will likely have
different actions based on which type of save they are executing. As such,
the code below works great, if the user selects Save; but if I select SaveAs
I get double saves for every possible subsequent combination/choice invoked
under this event.

The other problem that I have is that once I run the BeforeSave code once
.... it doesn't run again, unless I close & reopen. Obviously, what I want is
that every time the user clicks save, I want a new document/version created.


Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As
Boolean, _
Cancel As Boolean)
Dim WkBkCtrls As Variant
Determine
Cancel = True
Application.EnableEvents = False
If SaveAsUI = True Then
WkBkCtrls = MsgBox("Do you want this new workbook to have
document controls?", _
vbQuestion + vbYesNoCancel, "Add Document Controls")
If WkBkCtrls = vbCancel Then
Exit Sub
ElseIf WkBkCtrls = vbYes Then
xlDocCtrlCustFrm.Show
Exit Sub
ElseIf WkBkCtrls = vbNo Then
NoCtrls
Exit Sub
End If
Else
xlDocCtrlChoiceFrm.Show
Exit Sub
End If
Application.EnableEvents = True
End Sub


Public Sub UserForm_Initialize()
Me.CustDocName.Text = ActiveWorkbook.CustomDocumentProperties("DocName")
Me.CustMajNo.Text = ActiveWorkbook.CustomDocumentProperties("MajNo")
Me.CustMinNo.Text = ActiveWorkbook.CustomDocumentProperties("MinNo")
Me.CustDocVer.Text = _
ActiveWorkbook.CustomDocumentProperties("DocName") & "_v" & _
ActiveWorkbook.CustomDocumentProperties("MajNo") & "." & _
ActiveWorkbook.CustomDocumentProperties("MinNo")
End Sub

Private Sub CustDocName_Change()
ActiveWorkbook.CustomDocumentProperties("DocName") = CustDocName
Me.CustDocVer.Text = ActiveWorkbook.CustomDocumentProperties("DocName")
& "_v" & _
ActiveWorkbook.CustomDocumentProperties("MajNo") & "." & _
ActiveWorkbook.CustomDocumentProperties("MinNo")
End Sub

Private Sub CustMajNo_Change()
ActiveWorkbook.CustomDocumentProperties("MajNo") = CustMajNo
Me.CustDocVer.Text = ActiveWorkbook.CustomDocumentProperties("DocName")
& "_v" & _
ActiveWorkbook.CustomDocumentProperties("MajNo") & "." & _
ActiveWorkbook.CustomDocumentProperties("MinNo")
End Sub

Private Sub CustMinNo_Change()
ActiveWorkbook.CustomDocumentProperties("MinNo") = CustMinNo
Me.CustDocVer.Text = ActiveWorkbook.CustomDocumentProperties("DocName")
& "_v" & _
ActiveWorkbook.CustomDocumentProperties("MajNo") & "." & _
ActiveWorkbook.CustomDocumentProperties("MinNo")
End Sub

Private Sub CustOK_Click()
Dim Ext As Variant
Ext = Right(ActiveWorkbook.Name, 4)
Me.Hide
Application.EnableEvents = False
ActiveWorkbook.SaveAs (ActiveWorkbook.Path & "\" & _
ActiveWorkbook.CustomDocumentProperties("DocName") & _
"_v" & ActiveWorkbook.CustomDocumentProperties("MajNo") & "." & _
ActiveWorkbook.CustomDocumentProperties("MinNo") & Ext)
Application.EnableEvents = True
End Sub

Private Sub CustCancel_Click()
Me.Hide
xlDocCtrlChoiceFrm.Show
End Sub


Private Sub MajYes_Click()
Dim Ext As Variant
Ext = Right(ActiveWorkbook.Name, 4)
Me.Hide
ActiveWorkbook.CustomDocumentProperties("MajNo") = _
ActiveWorkbook.CustomDocumentProperties("MajNo") + 1
ActiveWorkbook.CustomDocumentProperties("MinNo") = 0
Application.EnableEvents = False
ActiveWorkbook.SaveAs (ActiveWorkbook.Path & "\" & _
ActiveWorkbook.CustomDocumentProperties("DocName") & _
"_v" & ActiveWorkbook.CustomDocumentProperties("MajNo") & "." & _
ActiveWorkbook.CustomDocumentProperties("MinNo") & Ext)
Application.EnableEvents = True
End Sub

Private Sub MajNo_Click()
Dim Ext As Variant
Ext = Right(ActiveWorkbook.Name, 4)
Me.Hide
ActiveWorkbook.CustomDocumentProperties("MinNo") = _
ActiveWorkbook.CustomDocumentProperties("MinNo") + 1
Application.EnableEvents = False
ActiveWorkbook.SaveAs (ActiveWorkbook.Path & "\" & _
ActiveWorkbook.CustomDocumentProperties("DocName") & _
"_v" & ActiveWorkbook.CustomDocumentProperties("MajNo") & "." & _
ActiveWorkbook.CustomDocumentProperties("MinNo") & Ext)
Application.EnableEvents = True
End Sub

Private Sub MajCancel_Click()
Me.Hide
End Sub

Private Sub MajCust_Click()
Me.Hide
xlDocCtrlCustFrm.Show
End Sub

Private Sub MajNoCtrl_Click()
Dim CtrlAnswer As Variant
Me.Hide
CtrlAnswer = MsgBox("This action will permanently erase all document
controls. " & _
"Are you sure you wish to proceed?", vbExclamation + vbYesNoCancel +
vbApplicationModal + _
vbDefaultButton2, "Remove Document Controls")
If CtrlAnswer = vbCancel Then
Exit Sub
ElseIf CtrlAnswer = vbYes Then
On Error Resume Next
ActiveWorkbook.CustomDocumentProperties("DocName").Delete
ActiveWorkbook.CustomDocumentProperties("UpdateNo").Delete
ActiveWorkbook.CustomDocumentProperties("OfficeSymb").Delete
ActiveWorkbook.CustomDocumentProperties("MajNo").Delete
ActiveWorkbook.CustomDocumentProperties("MinNo").Delete
Application.EnableEvents = False
Application.Dialogs(xlDialogSaveAs).Show
Application.EnableEvents = True
Exit Sub
ElseIf CtrlAnswer = vbNo Then
Me.Show
Exit Sub
End If
End Sub

The answer is in the event itself:
Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As
Boolean, _
Cancel As Boolean)

See the part that says SaveAsUI As Boolean? That tells you whether or not
they chose save or save as. You can look at this with msgbox(SaveAsUI ) or
just use it as a variable:
If SaveAsUI = True then....

--
-John http://www.jmbundy.blogspot.com/
Please rate when your question is answered to help us and others know what
is helpful.


"JT Klipfer" wrote:

> I'm 90% done with an Excel 2003 addin I've created to make my team's
> documents compliant with corporate document control requirements, which
> involves a couple of forms. I am not a pro at this, so my code may not look
> pretty, but it works ... sort of.
>
> I'm having issues with my BeforeSave event. I want to draw a distinction
> between when a user selects Save or SaveAs, as they will likely have
> different actions based on which type of save they are executing. As such,
> the code below works great, if the user selects Save; but if I select SaveAs
> I get double saves for every possible subsequent combination/choice invoked
> under this event.
>
> The other problem that I have is that once I run the BeforeSave code once
> ... it doesn't run again, unless I close & reopen. Obviously, what I want is
> that every time the user clicks save, I want a new document/version created.
>
>
> Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As
> Boolean, _
> Cancel As Boolean)
> Dim WkBkCtrls As Variant
> Determine
> Cancel = True
> Application.EnableEvents = False
> If SaveAsUI = True Then
> WkBkCtrls = MsgBox("Do you want this new workbook to have
> document controls?", _
> vbQuestion + vbYesNoCancel, "Add Document Controls")
> If WkBkCtrls = vbCancel Then
> Exit Sub
> ElseIf WkBkCtrls = vbYes Then
> xlDocCtrlCustFrm.Show
> Exit Sub
> ElseIf WkBkCtrls = vbNo Then
> NoCtrls
> Exit Sub
> End If
> Else
> xlDocCtrlChoiceFrm.Show
> Exit Sub
> End If
> Application.EnableEvents = True
> End Sub
>
>
> Public Sub UserForm_Initialize()
> Me.CustDocName.Text = ActiveWorkbook.CustomDocumentProperties("DocName")
> Me.CustMajNo.Text = ActiveWorkbook.CustomDocumentProperties("MajNo")
> Me.CustMinNo.Text = ActiveWorkbook.CustomDocumentProperties("MinNo")
> Me.CustDocVer.Text = _
> ActiveWorkbook.CustomDocumentProperties("DocName") & "_v" & _
> ActiveWorkbook.CustomDocumentProperties("MajNo") & "." & _
> ActiveWorkbook.CustomDocumentProperties("MinNo")
> End Sub
>
> Private Sub CustDocName_Change()
> ActiveWorkbook.CustomDocumentProperties("DocName") = CustDocName
> Me.CustDocVer.Text = ActiveWorkbook.CustomDocumentProperties("DocName")
> & "_v" & _
> ActiveWorkbook.CustomDocumentProperties("MajNo") & "." & _
> ActiveWorkbook.CustomDocumentProperties("MinNo")
> End Sub
>
> Private Sub CustMajNo_Change()
> ActiveWorkbook.CustomDocumentProperties("MajNo") = CustMajNo
> Me.CustDocVer.Text = ActiveWorkbook.CustomDocumentProperties("DocName")
> & "_v" & _
> ActiveWorkbook.CustomDocumentProperties("MajNo") & "." & _
> ActiveWorkbook.CustomDocumentProperties("MinNo")
> End Sub
>
> Private Sub CustMinNo_Change()
> ActiveWorkbook.CustomDocumentProperties("MinNo") = CustMinNo
> Me.CustDocVer.Text = ActiveWorkbook.CustomDocumentProperties("DocName")
> & "_v" & _
> ActiveWorkbook.CustomDocumentProperties("MajNo") & "." & _
> ActiveWorkbook.CustomDocumentProperties("MinNo")
> End Sub
>
> Private Sub CustOK_Click()
> Dim Ext As Variant
> Ext = Right(ActiveWorkbook.Name, 4)
> Me.Hide
> Application.EnableEvents = False
> ActiveWorkbook.SaveAs (ActiveWorkbook.Path & "\" & _
> ActiveWorkbook.CustomDocumentProperties("DocName") & _
> "_v" & ActiveWorkbook.CustomDocumentProperties("MajNo") & "." & _
> ActiveWorkbook.CustomDocumentProperties("MinNo") & Ext)
> Application.EnableEvents = True
> End Sub
>
> Private Sub CustCancel_Click()
> Me.Hide
> xlDocCtrlChoiceFrm.Show
> End Sub
>
>
> Private Sub MajYes_Click()
> Dim Ext As Variant
> Ext = Right(ActiveWorkbook.Name, 4)
> Me.Hide
> ActiveWorkbook.CustomDocumentProperties("MajNo") = _
> ActiveWorkbook.CustomDocumentProperties("MajNo") + 1
> ActiveWorkbook.CustomDocumentProperties("MinNo") = 0
> Application.EnableEvents = False
> ActiveWorkbook.SaveAs (ActiveWorkbook.Path & "\" & _
> ActiveWorkbook.CustomDocumentProperties("DocName") & _
> "_v" & ActiveWorkbook.CustomDocumentProperties("MajNo") & "." & _
> ActiveWorkbook.CustomDocumentProperties("MinNo") & Ext)
> Application.EnableEvents = True
> End Sub
>
> Private Sub MajNo_Click()
> Dim Ext As Variant
> Ext = Right(ActiveWorkbook.Name, 4)
> Me.Hide
> ActiveWorkbook.CustomDocumentProperties("MinNo") = _
> ActiveWorkbook.CustomDocumentProperties("MinNo") + 1
> Application.EnableEvents = False
> ActiveWorkbook.SaveAs (ActiveWorkbook.Path & "\" & _
> ActiveWorkbook.CustomDocumentProperties("DocName") & _
> "_v" & ActiveWorkbook.CustomDocumentProperties("MajNo") & "." & _
> ActiveWorkbook.CustomDocumentProperties("MinNo") & Ext)
> Application.EnableEvents = True
> End Sub
>
> Private Sub MajCancel_Click()
> Me.Hide
> End Sub
>
> Private Sub MajCust_Click()
> Me.Hide
> xlDocCtrlCustFrm.Show
> End Sub
>
> Private Sub MajNoCtrl_Click()
> Dim CtrlAnswer As Variant
> Me.Hide
> CtrlAnswer = MsgBox("This action will permanently erase all document
> controls. " & _
> "Are you sure you wish to proceed?", vbExclamation + vbYesNoCancel +
> vbApplicationModal + _
> vbDefaultButton2, "Remove Document Controls")
> If CtrlAnswer = vbCancel Then
> Exit Sub
> ElseIf CtrlAnswer = vbYes Then
> On Error Resume Next
> ActiveWorkbook.CustomDocumentProperties("DocName").Delete
> ActiveWorkbook.CustomDocumentProperties("UpdateNo").Delete
> ActiveWorkbook.CustomDocumentProperties("OfficeSymb").Delete
> ActiveWorkbook.CustomDocumentProperties("MajNo").Delete
> ActiveWorkbook.CustomDocumentProperties("MinNo").Delete
> Application.EnableEvents = False
> Application.Dialogs(xlDialogSaveAs).Show
> Application.EnableEvents = True
> Exit Sub
> ElseIf CtrlAnswer = vbNo Then
> Me.Show
> Exit Sub
> End If
> End Sub
>
>

I understand; that's what I actually already have in the BeforeSave event
(line 7); but I can't seem to understand why when a user selects SaveAs it
gives me the double save versus when they select only Save.

I think it has something to do with the Cancel property too and I've got
that in there ... but something just isn't lining up correctly.

"John Bundy" wrote:

> The answer is in the event itself:
> Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As
> Boolean, _
> Cancel As Boolean)
>
> See the part that says SaveAsUI As Boolean? That tells you whether or not
> they chose save or save as. You can look at this with msgbox(SaveAsUI ) or
> just use it as a variable:
> If SaveAsUI = True then....
>
> --
> -John http://www.jmbundy.blogspot.com/
> Please rate when your question is answered to help us and others know what
> is helpful.
>
>
> "JT Klipfer" wrote:
>
> > I'm 90% done with an Excel 2003 addin I've created to make my team's
> > documents compliant with corporate document control requirements, which
> > involves a couple of forms. I am not a pro at this, so my code may not look
> > pretty, but it works ... sort of.
> >
> > I'm having issues with my BeforeSave event. I want to draw a distinction
> > between when a user selects Save or SaveAs, as they will likely have
> > different actions based on which type of save they are executing. As such,
> > the code below works great, if the user selects Save; but if I select SaveAs
> > I get double saves for every possible subsequent combination/choice invoked
> > under this event.
> >
> > The other problem that I have is that once I run the BeforeSave code once
> > ... it doesn't run again, unless I close & reopen. Obviously, what I want is
> > that every time the user clicks save, I want a new document/version created.
> >
> >
> > Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As
> > Boolean, _
> > Cancel As Boolean)
> > Dim WkBkCtrls As Variant
> > Determine
> > Cancel = True
> > Application.EnableEvents = False
> > If SaveAsUI = True Then
> > WkBkCtrls = MsgBox("Do you want this new workbook to have
> > document controls?", _
> > vbQuestion + vbYesNoCancel, "Add Document Controls")
> > If WkBkCtrls = vbCancel Then
> > Exit Sub
> > ElseIf WkBkCtrls = vbYes Then
> > xlDocCtrlCustFrm.Show
> > Exit Sub
> > ElseIf WkBkCtrls = vbNo Then
> > NoCtrls
> > Exit Sub
> > End If
> > Else
> > xlDocCtrlChoiceFrm.Show
> > Exit Sub
> > End If
> > Application.EnableEvents = True
> > End Sub
> >
> >
> > Public Sub UserForm_Initialize()
> > Me.CustDocName.Text = ActiveWorkbook.CustomDocumentProperties("DocName")
> > Me.CustMajNo.Text = ActiveWorkbook.CustomDocumentProperties("MajNo")
> > Me.CustMinNo.Text = ActiveWorkbook.CustomDocumentProperties("MinNo")
> > Me.CustDocVer.Text = _
> > ActiveWorkbook.CustomDocumentProperties("DocName") & "_v" & _
> > ActiveWorkbook.CustomDocumentProperties("MajNo") & "." & _
> > ActiveWorkbook.CustomDocumentProperties("MinNo")
> > End Sub
> >
> > Private Sub CustDocName_Change()
> > ActiveWorkbook.CustomDocumentProperties("DocName") = CustDocName
> > Me.CustDocVer.Text = ActiveWorkbook.CustomDocumentProperties("DocName")
> > & "_v" & _
> > ActiveWorkbook.CustomDocumentProperties("MajNo") & "." & _
> > ActiveWorkbook.CustomDocumentProperties("MinNo")
> > End Sub
> >
> > Private Sub CustMajNo_Change()
> > ActiveWorkbook.CustomDocumentProperties("MajNo") = CustMajNo
> > Me.CustDocVer.Text = ActiveWorkbook.CustomDocumentProperties("DocName")
> > & "_v" & _
> > ActiveWorkbook.CustomDocumentProperties("MajNo") & "." & _
> > ActiveWorkbook.CustomDocumentProperties("MinNo")
> > End Sub
> >
> > Private Sub CustMinNo_Change()
> > ActiveWorkbook.CustomDocumentProperties("MinNo") = CustMinNo
> > Me.CustDocVer.Text = ActiveWorkbook.CustomDocumentProperties("DocName")
> > & "_v" & _
> > ActiveWorkbook.CustomDocumentProperties("MajNo") & "." & _
> > ActiveWorkbook.CustomDocumentProperties("MinNo")
> > End Sub
> >
> > Private Sub CustOK_Click()
> > Dim Ext As Variant
> > Ext = Right(ActiveWorkbook.Name, 4)
> > Me.Hide
> > Application.EnableEvents = False
> > ActiveWorkbook.SaveAs (ActiveWorkbook.Path & "\" & _
> > ActiveWorkbook.CustomDocumentProperties("DocName") & _
> > "_v" & ActiveWorkbook.CustomDocumentProperties("MajNo") & "." & _
> > ActiveWorkbook.CustomDocumentProperties("MinNo") & Ext)
> > Application.EnableEvents = True
> > End Sub
> >
> > Private Sub CustCancel_Click()
> > Me.Hide
> > xlDocCtrlChoiceFrm.Show
> > End Sub
> >
> >
> > Private Sub MajYes_Click()
> > Dim Ext As Variant
> > Ext = Right(ActiveWorkbook.Name, 4)
> > Me.Hide
> > ActiveWorkbook.CustomDocumentProperties("MajNo") = _
> > ActiveWorkbook.CustomDocumentProperties("MajNo") + 1
> > ActiveWorkbook.CustomDocumentProperties("MinNo") = 0
> > Application.EnableEvents = False
> > ActiveWorkbook.SaveAs (ActiveWorkbook.Path & "\" & _
> > ActiveWorkbook.CustomDocumentProperties("DocName") & _
> > "_v" & ActiveWorkbook.CustomDocumentProperties("MajNo") & "." & _
> > ActiveWorkbook.CustomDocumentProperties("MinNo") & Ext)
> > Application.EnableEvents = True
> > End Sub
> >
> > Private Sub MajNo_Click()
> > Dim Ext As Variant
> > Ext = Right(ActiveWorkbook.Name, 4)
> > Me.Hide
> > ActiveWorkbook.CustomDocumentProperties("MinNo") = _
> > ActiveWorkbook.CustomDocumentProperties("MinNo") + 1
> > Application.EnableEvents = False
> > ActiveWorkbook.SaveAs (ActiveWorkbook.Path & "\" & _
> > ActiveWorkbook.CustomDocumentProperties("DocName") & _
> > "_v" & ActiveWorkbook.CustomDocumentProperties("MajNo") & "." & _
> > ActiveWorkbook.CustomDocumentProperties("MinNo") & Ext)
> > Application.EnableEvents = True
> > End Sub
> >
> > Private Sub MajCancel_Click()
> > Me.Hide
> > End Sub
> >
> > Private Sub MajCust_Click()
> > Me.Hide
> > xlDocCtrlCustFrm.Show
> > End Sub
> >
> > Private Sub MajNoCtrl_Click()
> > Dim CtrlAnswer As Variant
> > Me.Hide
> > CtrlAnswer = MsgBox("This action will permanently erase all document
> > controls. " & _
> > "Are you sure you wish to proceed?", vbExclamation + vbYesNoCancel +
> > vbApplicationModal + _
> > vbDefaultButton2, "Remove Document Controls")
> > If CtrlAnswer = vbCancel Then
> > Exit Sub
> > ElseIf CtrlAnswer = vbYes Then
> > On Error Resume Next
> > ActiveWorkbook.CustomDocumentProperties("DocName").Delete
> > ActiveWorkbook.CustomDocumentProperties("UpdateNo").Delete
> > ActiveWorkbook.CustomDocumentProperties("OfficeSymb").Delete
> > ActiveWorkbook.CustomDocumentProperties("MajNo").Delete
> > ActiveWorkbook.CustomDocumentProperties("MinNo").Delete
> > Application.EnableEvents = False
> > Application.Dialogs(xlDialogSaveAs).Show
> > Application.EnableEvents = True
> > Exit Sub
> > ElseIf CtrlAnswer = vbNo Then
> > Me.Show
> > Exit Sub
> > End If
> > End Sub
> >
> >