Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeSave Problems
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("OfficeSym b").Delete ActiveWorkbook.CustomDocumentProperties("MajNo").D elete ActiveWorkbook.CustomDocumentProperties("MinNo").D elete Application.EnableEvents = False Application.Dialogs(xlDialogSaveAs).Show Application.EnableEvents = True Exit Sub ElseIf CtrlAnswer = vbNo Then Me.Show Exit Sub End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
beforesave | Excel Programming | |||
BeforeSave | Excel Programming | |||
Problems with BeforeSave and Application.EnableEvents = False | Excel Programming | |||
BeforeSave Sub | Excel Programming | |||
VBA - BeforeSave - NEED HELP | Excel Programming |