Scribd
Upload
4 views1 page

VBA Code to Lock Specific Columns

The document outlines a VBA macro that locks specific columns (A, B, and H) in an Excel worksheet named 'WL' and protects the sheet and workbook with defined passwords. It first unprotects the sheet if already protected, unlocks all cells, and then applies the locking to the specified columns while ensuring AutoFilters are enabled. Finally, it protects the sheet and workbook to restrict modifications such as hiding sheets and changing their properties.

Uploaded by

Amjad Hussain
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
4 views1 page

VBA Code to Lock Specific Columns

The document outlines a VBA macro that locks specific columns (A, B, and H) in an Excel worksheet named 'WL' and protects the sheet and workbook with defined passwords. It first unprotects the sheet if already protected, unlocks all cells, and then applies the locking to the specified columns while ensuring AutoFilters are enabled. Finally, it protects the sheet and workbook to restrict modifications such as hiding sheets and changing their properties.

Uploaded by

Amjad Hussain
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

Sub LockSpecificColumnsAndProtectSheet()

Dim ws As Worksheet
Set ws = [Link]("WL")

' Define your password


Dim strPassword As String
Dim wbPassword As String
strPassword = "Oman@2024" ' Password for sheet protection
wbPassword = "OmanWb2024" ' Password for workbook protection

' Unprotect the sheet with the password if it's already protected
[Link] Password:=strPassword

' Unlock all cells on the sheet


[Link] = False

' Lock entire columns A, B, and H


[Link]("A").Locked = True
[Link]("B").Locked = True
[Link]("H").Locked = True

' Make sure that AutoFilters are enabled before protecting the sheet
If [Link] = False Then
[Link]("A1").AutoFilter
End If

' Protect the sheet with a password to enforce the locking


' Enable AllowFiltering to keep the drop-down filter option available
[Link] Password:=strPassword, DrawingObjects:=True, Contents:=True,
Scenarios:=True, AllowFiltering:=True

' Protect workbook to prevent hiding/unhiding sheets, renaming sheets,


moving/copying sheets, and changing tab colors
[Link] Password:=wbPassword, Structure:=True, Windows:=False
End Sub

You might also like