Before-Close-Anweisung
04.03.2005 07:57:34
Andreas
ich habe den folgenden Code in mein Xls-Tool eingebaut und möchte darüber das Schließen der xls-Mappe steuern. Die Case-Anweisung 'Ja' und 'Abbrechen' funktioniert auch einwandfrei. Wenn ich aber das Dialogfeld mit 'Nein' betsätige erscheint nochmals zusätzlich das Microsoft-Dialogfeld Speichern (Ja, Nein ,...).
Kann mir jemand sagen wo der Fehler liegt?
Vielen dank im voraus,
Andreas
-------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Erzwingen, dass Speichern abgefragt wird
Msg = "Möchten Sie die geänderte Datei " & Me.Name & " speichern?"
Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel)
Select Case Ans
Case vbYes
If (Workbooks(Wrkbk).ActiveSheet.Name = "Einleitung" Or Workbooks(Wrkbk).ActiveSheet.Name = "Annahmen" Or Workbooks(Wrkbk).ActiveSheet.Name = "CO-Basisdaten" Or Workbooks(Wrkbk).ActiveSheet.Name = "Bilanz" Or Workbooks(Wrkbk).ActiveSheet.Name = "GuV" Or Workbooks(Wrkbk).ActiveSheet.Name = "Finanzplan" Or Workbooks(Wrkbk).ActiveSheet.Name = "Übersicht-LBZ" Or Workbooks(Wrkbk).ActiveSheet.Name = "Umsatzerlöse" Or Workbooks(Wrkbk).ActiveSheet.Name = "Aufwendungen" Or Workbooks(Wrkbk).ActiveSheet.Name = "AfA" Or Workbooks(Wrkbk).ActiveSheet.Name = "Personal" Or Workbooks(Wrkbk).ActiveSheet.Name = "Nebenrechnung") Then
Workbooks(Wrkbk).Unprotect (pwd_wrkbk)
Workbooks(Wrkbk).ActiveSheet.Visible = False
Workbooks(Wrkbk).Protect Password:=pwd_wrkbk, Structure:=True, Windows:=False
End If
Workbooks(Wrkbk).Sheets("Hauptmenu").Select
Workbooks(Wrkbk).Sheets("Hauptmenu").Range("C3") = ""
Application.DisplayFormulaBar = True
Application.DisplayStatusBar = True
Me.Save
Case vbNo
Workbooks(Wrkbk).Sheets("Hauptmenu").Select
Workbooks(Wrkbk).Sheets("Hauptmenu").Range("C3") = ""
Application.DisplayFormulaBar = True
Application.DisplayStatusBar = True
Me.Saved = True
Cancel = False
Case vbCancel
Cancel = True
End Select
End Sub
Anzeige