VBA Lock Formula (given) Doesn't Allow Filtering/Highlighting/Font Change

CuriousForge

New Member
Joined
Aug 20, 2018
Messages
15
I do not how to write VBA codes, however this VBA formula I found on the internet allows me to lock cells after data entry as intended.

It doesn't allow Filtering cells, cell highlighting or font modification for protected cells. Can someone help me modify this so it allows filtering/highlighting cells/font modifications.

Private Sub Worksheet_Change(ByVal Target As Range)
Const strPassword = "secret"
Dim rngEdit As Range
Dim cel As Range
Set rngEdit = Intersect(Range("B2:B20,D2:D20"), Target)
If Not rngEdit Is Nothing Then
Application
.EnableEvents = False
Me.Unprotect Password:=strPassword
For Each cel In rngEdit
cel
.Locked = (cel.Value <> "")
Next cel
Me.Protect Password:=strPassword
Application
.EnableEvents = True
End If
End Sub
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,685
Office Version
  1. 365
Platform
  1. Windows
Try:
Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)

    Const strPassword = "secret"
    
    Dim rngEdit As Range
    Dim cel     As Range

    Set rngEdit = Intersect(Range("B2:B20,D2:D20"), Target)
    
    If Not rngEdit Is Nothing Then
        Application.EnableEvents = False
        
        Me.Unprotect Password:=strPassword
        
        For Each cel In rngEdit
            cel.Locked = (cel.Value <> "")
        Next cel
        
        Me.Protect Password:=strPassword, AllowFormattingCells:=True, AllowFiltering:=True
        
        Application.EnableEvents = True
    End If
    
End Sub
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,476
Messages
5,529,077
Members
409,849
Latest member
J7House1984
Top