Option Explicit
Option Compare Text
Dim MyRange1 As Range, MyRange2 As Range, MyRange3 As Range
Dim AllRange As Range
Dim Pass1 As String
Dim Pass2 As String
Dim Pass3 As String
Dim PassAll As String
Const Pwd = "test"
Private Sub SetRanges()
Pass1 = "Yes1"
Set MyRange1 = Union([B8], [B10], [B12])
Pass2 = "Yes2"
Set MyRange2 = Union([B8], [B10], [B12], [D8], [D10], [D12])
Pass3 = "Yes3"
Set MyRange3 = Union([F8], [F10], [F12])
PassAll = "Yes99"
Set AllRange = Union(MyRange1, MyRange2, MyRange3)
End Sub
Private Sub Worksheet_Activate()
TextBox1 = ""
TextBox1.Activate
End Sub
Sub UnprotectCell(MyRange As Range)
ActiveSheet.Unprotect Password:=Pwd
MyRange.Locked = False
MyRange.Interior.ColorIndex = 4
ActiveSheet.Protect Password:=Pwd
End Sub
Sub ProtectCell()
ActiveSheet.Unprotect Password:=Pwd
AllRange.Locked = True
AllRange.Interior.ColorIndex = 8
ActiveSheet.Protect Password:=Pwd
End Sub
Private Sub TextBox1_Change()
SetRanges
Select Case TextBox1
Case Is = Pass1
ProtectCell
UnprotectCell MyRange1
CleanUp
Case Is = Pass2
ProtectCell
UnprotectCell MyRange2
CleanUp
Case Is = Pass3
ProtectCell
UnprotectCell MyRange3
CleanUp
Case Is = PassAll
ProtectCell
UnprotectCell AllRange
CleanUp
Case Else
ProtectCell
CleanUp
End Select
End Sub
Sub CleanUp()
Set MyRange1 = Nothing
Set MyRange2 = Nothing
Set MyRange3 = Nothing
Set AllRange = Nothing
End Sub
Sub LockIt()
TextBox1 = ""
End Sub
|