AW: Environ - Datenblätter anzeigen/ausblenden
05.09.2014 17:05:25
ChrisL
Hi Danyel
Ohne VBA Kenntnisse natürlich etwas schwierig, aber hier mal ein Anfang.
Der Schutz mit Excel Passwort ist nie ganz sicher.
Die berechtigten Benutzer stehen in Blatt "Benutzer" Spalte A. Ein leeres Blatt namens "Stopp" ist erforderlich.
Blatt "Benutzer" würde ich mit xlveryhidden manuell ausblenden.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim sPW As String
sPW = "Passwort"
On Error Resume Next
Application.EnableEvents = False
ThisWorkbook.Unprotect sPW
Call SheetsAusblenden
Cancel = True
If SaveAsUI Then
Application.Dialogs(xlDialogSaveAs).Show , xlOpenXMLWorkbookMacroEnabled
Else
ThisWorkbook.Save
End If
Call SheetsEinblenden
ThisWorkbook.Protect sPW
Application.EnableEvents = True
ThisWorkbook.Saved = True
End Sub
Private Sub Workbook_Open()
Dim sPW As String
sPW = "Passwort"
If WorksheetFunction.CountIf(Worksheets("Benutzer").Columns(1), Environ("Username")) > 0 Then
ThisWorkbook.Unprotect sPW
Call SheetsEinblenden
ThisWorkbook.Protect sPW
ThisWorkbook.Saved = True
End If
End Sub
Private Sub SheetsAusblenden()
With ThisWorkbook
.Sheets("Stopp").Visible = True
.Sheets("Tabelle1").Visible = xlVeryHidden
.Sheets("Tabelle2").Visible = xlVeryHidden
.Sheets("Tabelle3").Visible = xlVeryHidden
End With
End Sub
Private Sub SheetsEinblenden()
With ThisWorkbook
.Sheets("Tabelle1").Visible = True
.Sheets("Tabelle2").Visible = True
.Sheets("Tabelle3").Visible = True
.Sheets("Stopp").Visible = xlVeryHidden
End With
End Sub
cu
Chris