Readdy Write  
0,00 €
Your View Money
Views: Count
Self 20% 0
Your Content 60% 0

Users by Links 0
u1*(Content+Views) 10% 0
Follow-Follower 0
s2*(Income) 5% 0

Count
Followers 0
Login Register as User

Excel Arbeitsmappe schützen per Makro

07.06.2019 (👁11733)

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