Excel Arbeitsmappe schützen per Makro
Verhindern, dass Tabellenblätter umbenannt werden
Mit dem makro
Die Excel
Arbeitsmappe wird dabei vor Veränderungen mit dem folgenden Makro geschützt:
wb.Protect °const_Password, Structure:=True |
Vba Makro Code
Option Explicit Public Function Protect_Workbook() '--------< Protect_Workbook() >-------- '*schuetzen der Arbeitsmappe, disable Umbenennen 'On Error Resume Next
Dim wb As Workbook Set wb = ActiveWorkbook wb.Protect °const_Password, Structure:=True, Windows:=True '--------</ Protect_Workbook() >-------- End Function Public Function UnProtect_Workbook() '--------< UnProtect_Workbook() >-------- 'On Error Resume Next
Dim wb As Workbook Set wb = ActiveWorkbook wb.Unprotect °const_Password '--------</ UnProtect_Workbook() >-------- End Function |
Vba Makro Code zum sperren des Arbeitsblattes und für die ganze Mappe als Schutz
Option Explicit
Public Function Protect_Workbook() '--------< Protect_Workbook() >-------- '*schuetzen der Arbeitsmappe, disable Umbenennen 'On Error Resume Next
Dim wb As Workbook Set wb = ActiveWorkbook wb.Protect °const_Password, Structure:=True, Windows:=True '--------</ Protect_Workbook() >-------- End Function
Public Function UnProtect_Workbook() '--------< UnProtect_Workbook() >-------- 'On Error Resume Next
Dim wb As Workbook Set wb = ActiveWorkbook wb.Unprotect °const_Password '--------</ UnProtect_Workbook() >-------- End Function
Public Function Protect_Worksheets() '--------< Protect_Worksheets() >-------- '*hide all worksheets where name starts with _ '*hide: very hidden On Error Resume Next
Dim wb As Workbook Set wb = ActiveWorkbook Dim ws As Worksheet For Each ws In wb.Sheets If ws.Visible = xlSheetVisible Then Protect_Worksheet ws End If Next '--------</ Protect_Worksheets() >-------- End Function
Public Function Protect_Worksheet(ByRef ws As Worksheet) '--------< Protect_Worksheets() >-------- '*protect worksheet If ws.Visible = xlSheetVisible Then '*protect all visible worksheets
'< check: isProtected > If ws.ProtectContents = True Or ws.ProtectDrawingObjects = True Or ws.ProtectScenarios = True Then ws.Unprotect °const_Password End If '</ check: isProtected >
'< worksheet.Protect > ws.Protect °const_Password, Contents:=True _ , DrawingObjects:=False, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowInsertingHyperlinks:=True _ , Scenarios:=True, UserInterfaceOnly:=True, AllowInsertingColumns:=False, AllowInsertingRows:=False _ , AllowDeletingColumns:=False, AllowDeletingRows:=False _ , AllowSorting:=False, AllowFiltering:=True, AllowUsingPivotTables:=True '< worksheet.Protect > End If '--------</ Protect_Worksheets() >-------- End Function
Public Function Unprotect_Worksheets() '--------< Unprotect_Worksheets() >-------- 'unprotect all worksheets On Error Resume Next
Dim wb As Workbook Set wb = ActiveWorkbook Dim ws As Worksheet For Each ws In wb.Sheets
If ws.Visible = xlSheetVisible Then ws.Unprotect °const_Password End If Next '--------</ Unprotect_Worksheets() >-------- End Function
|
Die Datei liegt als Download bei