Private Sub Workbook_BeforeClose(Cancel As Boolean) if file è modificato then msg modificato if file non modificato then msg non modificato end if
Option Explicit Dim modificato As Boolean Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) modificato = True End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) If modificato = True Then MsgBox "file modificato" Else MsgBox "file non modificato" End If End Sub
Option Explicit Dim modificato As Boolean Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) modificato = True End Sub Private Sub Workbook_Open() Dim name1 As String Dim CurFolder As String Dim DestFolder As String Application.ScreenUpdating = False name1 = Foglio2.Range("Z1").Value CurFolder = ActiveWorkbook.path DestFolder = CurFolder & "\" & name1 & "\" If Dir(DestFolder, vbDirectory) = "" Then MkDir DestFolder Open DestFolder & "\accessi.log" For Append As #1 Print #1, Application.UserName, Now & " ACCESSO" Close #1 Application.ScreenUpdating = True End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim name1 As String Dim CurFolder As String Dim DestFolder As String Dim risposta As String name1 = Foglio2.Range("Z1").Value CurFolder = ActiveWorkbook.path DestFolder = CurFolder & "\" & name1 & "\" If Dir(DestFolder, vbDirectory) = "" Then MkDir DestFolder Open DestFolder & "\accessi.log" For Append As #1 If modificato = True Then risposta = MsgBox("Vuoi salvare le modifiche apportate ?", vbYesNo) Select Case risposta Case Is = vbYes 'file modificato e salvato Print #1, Application.UserName, Now & " CHIUSURA" & " modificato " ThisWorkbook.Save Case Is = vbNo 'file modificato ma non salvato Print #1, Application.UserName, Now & " CHIUSURA" & " non modificato " ThisWorkbook.Saved = True End Select Else 'file non modificato Print #1, Application.UserName, Now & " CHIUSURA" & " non modificato " End If Print #1, "-------------------------------------------------------" Close #1 End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim name1, name2 As String Dim CurFolder As String Dim DestFolder As String Dim risposta As String name1 = Foglio2.Range("Z1").Value name2 = Foglio2.Range("Z3").Value CurFolder = ActiveWorkbook.path DestFolder = CurFolder & "\" & name1 & "\" If Dir(DestFolder, vbDirectory) = "" Then MkDir DestFolder Open DestFolder & "\accessi.log" For Append As #1 If modificato = True Then risposta = MsgBox("Salvare le modifiche apportate a '" & name2 & "'?", vbExclamation + vbYesNoCancel, "Microsoft Office Question") Select Case risposta Case Is = vbYes 'file modificato e salvato Print #1, Application.UserName, Now & " CHIUSURA" & " modificato " ThisWorkbook.Save Case Is = vbNo 'file modificato ma non salvato Print #1, Application.UserName, Now & " CHIUSURA" & " non modificato " ThisWorkbook.Saved = True End Select Else 'file non modificato Print #1, Application.UserName, Now & " CHIUSURA" & " non modificato " End If Print #1, "-------------------------------------------------------" Close #1 End Sub
Case Is = vbCancel
Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim name1 As String, name2 As String 'modificata Dim CurFolder As String Dim DestFolder As String Dim risposta As String name1 = Foglio2.Range("Z1").Value name2 = Foglio2.Range("Z3").Value CurFolder = ActiveWorkbook.path DestFolder = CurFolder & "\" & name1 & "\" If Dir(DestFolder, vbDirectory) = "" Then MkDir DestFolder Open DestFolder & "\accessi.log" For Append As #1 If modificato = True Then risposta = MsgBox("Salvare le modifiche apportate a '" & name2 & "' ?", vbExclamation + vbYesNoCancel, "Microsoft Office Question") Select Case risposta Case Is = vbYes 'file modificato e salvato Print #1, Application.UserName, Now & " CHIUSURA" & " modificato " ThisWorkbook.Save Case Is = vbNo 'file modificato ma non salvato Print #1, Application.UserName, Now & " CHIUSURA" & " non modificato " ThisWorkbook.Saved = True Case Is = vbCancel 'uscita annullata Cancel = True 'annullo l'evento Close Close #1 'chiudo il file accessi.log Exit Sub 'abbandono la macro End Select Else 'file non modificato Print #1, Application.UserName, Now & " CHIUSURA" & " non modificato " End If Print #1, "-------------------------------------------------------" Close #1 End Sub
ActiveWorkbook.Close False End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim name1 As String, name2 As String 'modificata Dim CurFolder As String Dim DestFolder As String Dim risposta As String name1 = Foglio2.Range("Z1").Value name2 = Foglio2.Range("Z3").Value CurFolder = ActiveWorkbook.path DestFolder = CurFolder & "\" & name1 & "\" If Dir(DestFolder, vbDirectory) = "" Then MkDir DestFolder Open DestFolder & "\accessi.log" For Append As #1 If modificato = True Then risposta = MsgBox("Salvare le modifiche apportate a '" & name2 & "' ?", vbExclamation + vbYesNoCancel, "Microsoft Office Question") Select Case risposta Case Is = vbYes 'file modificato e salvato Print #1, Application.UserName, Now & " CHIUSURA" & " modificato " ThisWorkbook.Save Case Is = vbNo 'file modificato ma non salvato Print #1, Application.UserName, Now & " CHIUSURA" & " non modificato " ThisWorkbook.Saved = True Case Is = vbCancel 'uscita annullata Cancel = True 'annullo l'evento Close Close #1 'chiudo il file accessi.log Exit Sub 'abbandono la macro End Select Else 'file non modificato Print #1, Application.UserName, Now & " CHIUSURA" & " non modificato " End If Print #1, "-------------------------------------------------------" Close #1 ActiveWorkbook.Close False '<<< aggiunta End Sub
Application.EnableEvents = False 'disabiliti il controllo degli eventi ActiveWorkbook.Close False '<<< aggiunta Application.EnableEvents = True 'riabiliti il controllo degli eventi
Private Sub Workbook_BeforeClose(Cancel As Boolean) Cancel = True CloseNoSave End Sub Private Sub CloseNoSave Application.EnableEvents = False ThisWorkbook.Close SaveChanges:=False Application.EnableEvents = True End Sub
Option Explicit Dim modificato As Boolean Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) modificato = True End Sub Private Sub Workbook_Open() Dim Urec As String Dim path As String Dim name1 As String Dim CurFolder, DestFolder As String '--------------------------------------------------------------------------- 'username accesso/chiusura all'avvio/chiusura name1 = Foglio1.Range("A1").Value CurFolder = ActiveWorkbook.path DestFolder = CurFolder & "\" & name1 & "\" If Dir(DestFolder, vbDirectory) = "" Then MkDir DestFolder Open DestFolder & "\accessi_ACTIONLIST_C.log" For Append As #1 'Open path & "\accessi_PROVA.log" For Append As #1 Urec = Cells(Rows.Count, 1).End(xlUp).Row + 1 Print #1, Application.UserName, Now & " ACCESSO" Close #1 '-------------------------------------------------------------------------- End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim Urec As String Dim path As String Dim name1 As String Dim CurFolder, DestFolder As String '--------------------------------------------------------------------------- 'username accesso/chiusura all'avvio/chiusura name1 = Foglio1.Range("A1").Value CurFolder = ActiveWorkbook.path DestFolder = CurFolder & "\" & name1 & "\" If Dir(DestFolder, vbDirectory) = "" Then MkDir DestFolder Open DestFolder & "\accessi_ACTIONLIST_C.log" For Append As #1 'Open path & "\accessi_PROVA.log" For Append As #1 If modificato = True Then Urec = Cells(Rows.Count, 1).End(xlUp).Row + 1 Print #1, Application.UserName, Now & " CHIUSURA MODIFICATO" Print #1, "-------------------------------------------------" Close #1 End If If modificato = False Then Urec = Cells(Rows.Count, 1).End(xlUp).Row + 1 Print #1, Application.UserName, Now & " CHIUSURA NON MODIFICATO " Print #1, "-------------------------------------------------" Close #1 End If '-------------------------------------------------------------------------- End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) modificato = True End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim Urec As String Dim path As String Dim name1 As String Dim CurFolder, DestFolder As String '--------------------------------------------------------------------------- 'username accesso/chiusura all'avvio/chiusura name1 = Foglio1.Range("A1").Value CurFolder = ActiveWorkbook.path DestFolder = CurFolder & "\" & name1 & "\" If Dir(DestFolder, vbDirectory) = "" Then MkDir DestFolder Open DestFolder & "\accessi.log" For Append As #1 'Open path & "\accessi_PROVA.log" For Append As #1 If ThisWorkbook.Saved = False Then 'ThisWorkbook.Save Urec = Cells(Rows.Count, 1).End(xlUp).Row + 1 Print #1, Application.UserName, Now & " CHIUSURA MODIFICATO" Print #1, "-------------------------------------------------" Close #1 ElseIf ThisWorkbook.Saved = True Then Urec = Cells(Rows.Count, 1).End(xlUp).Row + 1 Print #1, Application.UserName, Now & " CHIUSURA NON MODIFICATO " Print #1, "-------------------------------------------------" Close #1 Else If ThisWorkbook.Saved = True Then Urec = Cells(Rows.Count, 1).End(xlUp).Row + 1 Print #1, Application.UserName, Now & " CHIUSURA NON MODIFICATO " Print #1, "-------------------------------------------------" Close #1 End If End If '-------------------------------------------------------------------------- End Sub
Else If ThisWorkbook.Saved = True Then Urec = Cells(Rows.Count, 1).End(xlUp).Row + 1 Print #1, Application.UserName, Now & " CHIUSURA NON MODIFICATO " Print #1, "-------------------------------------------------" Close #1