#

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

Mobile

.

0123movie.net