I haven't used VBA before so I'm really new to this :-) The below is the code I am currently using , and simply need to lock all area's of the sheet (with out using the sheet name) apart from A13:A377, B1, D3:D4, D13:D377, F13:I377. I can't protect the sheet because the VBA won't work. Help please...
Private Sub Worksheet_Change(ByVal Target As Range)
' To allow multiple selections in a Drop Down List in Excel (without repetition)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Column = 1 Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & " & " & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
I don't see any relation between your description and the code you have shared! Please find below a proposal to unlock a union of cells and protect the sheet (without password!)
Option Explicit
Sub UnlockCells_and_Protect()
Dim actSheet As String
actSheet = "Sheet2" ' choose whatever you need
'actSheet = ActiveSheet.Name
'actSheet = Sheets(3).Name
'actsheet = "SpecialSheet"
Call UnprotectSheet(actSheet)
Call LockAll(actSheet)
Call UnlockRange(actSheet, "A13:A377,B1,D3:D4,D13:D377,F13:I377")
Call ProtectSheet(actSheet)
End Sub
Sub UnlockRange(sheetName As String, RangeReference As String)
With Sheets(sheetName).Range(RangeReference)
.Locked = False
.FormulaHidden = False
'you might want to mark the unlocked cells for debugging
Sheets(sheetName).Range(RangeReference).Interior.Color = vbYellow
End With
End Sub
Sub ProtectSheet(sheetName As String)
Sheets(sheetName).Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
Sub ProtectActiveSheet()
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
Sub UnprotectSheet(sheetName As String)
Sheets(sheetName).Unprotect
End Sub
Sub UnprotectActiveSheet()
ActiveSheet.Unprotect
End Sub
Sub LockAll(sheetName As String)
Sheets(sheetName).Cells.Locked = True
Sheets(sheetName).Cells.FormulaHidden = False
'if you marked the unlocked cells yellow you change
'them back to white with lock/unlock all
Sheets(sheetName).Cells.Interior.Color = vbWhite
End Sub
Sub UnlockAll(sheetName As String)
Sheets(sheetName).Cells.Locked = False
Sheets(sheetName).Selection.FormulaHidden = False
'if you marked the unlocked cells yellow you change
'them back to white with lock/unlock all
Sheets(sheetName).Cells.Interior.Color = vbWhite
End Sub