This post will guide you how to lock hidden columns in Excel 2010/2/13/2016. How do I protect and lock hidden columns in Excel.

1. Lock and Protect Hidden Columns

You can try to hide the selected columns or unhide them again. But if you want to lock all hidden columns in your worksheet. How to do it. Here are the steps:

#1 select one or two columns that you want to hide. And right click on it, and select Hide from the popup menu list.

#2 select all your data or click Select All button at the intersection of the row number and column letter.

#3 right click on it, and select Format Cells from the popup menu list. And the Format Cells dialog will open.

#4 switch to Protection tab, and make sure boot the Locked and Hidden Check boxes are selected. Click OK button.

#5 go to REVIEW tab, click Protect Sheet command under Changes group. And the Protect sheet dialog will open.

#6 type your password to protect your worksheet. And reenter password to protect. Click Ok button.

#7 the hidden columns have been locked. And you are not able to unhide them.

2. Lock Hidden Columns using VBA Code

Now, let’s delve into the second method—harnessing the prowess of VBA code for a more advanced and tailored approach to securing hidden columns.

Press Alt + F11 to open the Visual Basic for Applications editor.

Insert a new module: Right-click on any item in the Project Explorer and choose Insert > Module.

Copy and paste the provided VBA code into the module.

Sub LockHiddenColumns()
    Dim ws As Worksheet
    Dim rng As Range
    
    ' Set the worksheet variable to the active sheet
    Set ws = ActiveSheet
    
    ' Set the range variable to the selected columns
    Set rng = Selection
    
    ' Check if any columns are hidden
    If WorksheetFunction.Count(rng.EntireColumn.Hidden) > 0 Then
        ' Protect the worksheet
        ws.Protect Password:="YourPassword", UserInterfaceOnly:=True
        ' Set the Locked property for the selected range to True
        rng.Locked = True
    Else
        ' If no columns are hidden, display a message
        MsgBox "No columns are hidden. Please hide some columns and try again.", vbInformation
    End If
End Sub

Close the VBA editor.

Select the columns you want to hide and lock.

Run the macro by pressing Alt + F8, selecting “LockHiddenColumns,” and clicking “Run.”

Ensure you replace “YourPassword” in the code with your desired password. This VBA code checks if any columns are hidden, protects the worksheet, and locks the hidden columns.

3. Video: Lock Hidden Columns

This Excel video tutorial where we’ll delve into the intricacies of securing your data. In this guide, we’ll explore two methods to lock hidden columns: utilizing Excel’s built-in Protect Sheet feature and employing the power of a VBA Macro.