-
Notifications
You must be signed in to change notification settings - Fork 0
/
Protect Sheets
64 lines (56 loc) · 2.46 KB
/
Protect Sheets
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
Private Sub Workbook_Open()
ProtectSheets
End Sub
------------------------------------------------------------------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ProtectSheets
End Sub
------------------------------------------------------------------------------
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim protectedSheets As Variant
Dim ws As Worksheet
Dim password As String
' List of sheets to protect
protectedSheets = Array("TEMPLATE_ALL", "Audio Out-House", "Summary", "HOLIDAYS")
' Check if the changed sheet is one of the protected sheets
On Error Resume Next
Set ws = ThisWorkbook.Sheets(Target.Worksheet.Name)
On Error GoTo 0
If Not ws Is Nothing And IsInArray(ws.Name, protectedSheets) Then
' Check if the sheet is protected
If ws.ProtectContents Then
' Prompt the user to enter the password to unprotect the sheet
password = InputBox("Enter the password to unprotect the sheet:", "Password")
' Check if the entered password matches the preset password
If password = "1234" Then
' Unprotect the sheet to allow editing
ws.Unprotect password:="1234"
Else
MsgBox "Incorrect password. The sheet will remain protected.", vbExclamation
Application.EnableEvents = False
Target.Offset(1, 0).Select ' Move to the next cell to avoid an infinite loop
Application.EnableEvents = True
End If
End If
End If
End Sub
------------------------------------------------------------------------------
Private Sub ProtectSheets()
Dim protectedSheets As Variant
Dim ws As Worksheet
' List of sheets to protect
protectedSheets = Array("TEMPLATE_ALL", "Audio Out-House", "Summary", "HOLIDAYS")
' Loop through each protected sheet and protect with the preset password
For Each sheetName In protectedSheets
On Error Resume Next
Set ws = ThisWorkbook.Sheets(sheetName)
On Error GoTo 0
If Not ws Is Nothing Then
ws.Protect password:="1234", UserInterfaceOnly:=True
End If
Next sheetName
End Sub
------------------------------------------------------------------------------
Function IsInArray(val As Variant, arr As Variant) As Boolean
IsInArray = (UBound(Filter(arr, val)) > -1)
End Function