Excel VBA Protect Sheet Allow Select Locked Cells in 4 Easy Steps

In this Excel VBA Protect Sheet Allow Select Locked Cells Tutorial, you learn how to:

Protect a sheet; and
Allow the user to select locked cells;

Using Excel macros.

This Excel VBA Protect Sheet Allow Select Locked Cells Tutorial is accompanied by an Excel workbook with the data and VBA code I use when describing the step-by-step process below. Get this example workbook (for free) by clicking the button below.

The VBA code in the Excel workbook that accompanies this Excel VBA Protect Sheet Allow Select Locked Cells Tutorial is (always) stored in the Visual Basic Editor (VBE). If you don't know how to work with the VBE, I suggest you read my Visual Basic Editor (VBE) Tutorial. I link to this Tutorial in the Related Excel Macro and VBA Training Materials and Resources Section below.

Table of Contents

The following Excel Macro and VBA Tutorials may help you better understand and implement the contents below.

Tutorials about general macro and VBA constructs and structures:
- Tutorials for Beginners:
  - Excel Macros: Click here to open.
  - Excel VBA: Click here to open.
- Enable macros in Excel: Click here to open.
- Work with the Visual Basic Editor (VBE): Click here to open.
- Create Sub procedures: Click here to open.
- Refer to objects (click here to open), including:
  - Sheets: Click here to open.
  - Cell ranges: Click here to open.
- Work with:
  - Properties: Click here to open.
  - Methods: Click here to open.
Tutorials with practical VBA applications and macro examples:
- Activate workbook: Click here to open.
- Create new workbook: Click here to open.
- Open workbook: Click here to open.
- Delete sheet: Click here to open.

This Excel VBA Protect Sheet Allow Select Locked Cells Tutorial is part of a more comprehensive series of Excel VBA Protect or Unprotect Sheet Tutorials.

Excel VBA Protect Sheet Without Password in 2 Easy Steps: Click here to open.
Excel VBA Unprotect Sheet Without Password in 2 Easy Steps: Click here to open.
Excel VBA Unprotect Sheet with Password in 2 Easy Steps: Click here to open.
Excel VBA Protect Sheet with Password in 2 Easy Steps: Click here to open.
Excel VBA Protect Sheet Allow Filter in 2 Easy Steps: Click here to open.

You can find more Excel and VBA Tutorials in the organized Tutorials Archive: Click here to visit the Archives.

If you want to learn how to automate Excel (and save time) by working with macros and VBA, you may be interested in the following Premium Excel Macro and VBA Training Materials:

Premium Courses at the Power Spreadsheets Academy: Click here to open.
Books at the Power Spreadsheets Library: Click here to open.
VBA Cheat Sheets: Click here to open.

If you want to save time when working with macros and VBA, you may be interested in AutoMacro: Click here to learn more about AutoMacro (affiliate link). AutoMacro is an add-in for VBA that installs directly into the VBE. Depending on the version, AutoMacro comes loaded with:

Code generators.
An extensive code library.
The ability to create your own code library.
Advanced coding tools.

If you need consulting services, you may want to consider working with ExcelRescue. ExcelRescue is my usual suggestion for people who (like you) may need help with Excel tasks/projects: Click here to visit ExcelRescue (affiliate link).

The VBA Protect Sheet Allow Select Locked Cells Snippet Template/Structure

The following is the VBA protect sheet allow select locked cells snippet template/structure I explain (step-by-step) in the Sections below.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/protect-select-locked-cells/
With WorkbookObjectReference.WorksheetObjectReference
    .RangeObjectReference.Locked = True
    .Protect
    .EnableSelection = xlNoRestrictions
End With

The Example Before VBA Protect Sheet Allow Select Locked Cells

The example workbook has a single (empty) worksheet.

The name of the example worksheet is “Protect Select Locked Cells”.
This is the sheet the VBA protect sheet allow select locked cells example macro I create (by following the step-by-step process below) works with. In other words: The VBA protect sheet allow select locked cells example macro:
- Protects this sheet; and
- Allows the user to select locked cells.

The image below displays the example worksheet before I execute the VBA protect sheet allow select locked cells example macro.

Before I execute the VBA protect sheet allow select locked cells example macro, the example worksheet is unprotected. Notice the Protect Sheet button (inside the Protect group of commands) in the Excel Ribbon (indicating the example worksheet is (currently) unprotected).

Example Excel workbook before VBA protect sheet allow select locked cells example macro

Step 1: Refer to Sheet

Refer to the sheet you want to protect (while allowing the user to select locked cells).

In other words: Create a VBA expression that returns a Worksheet object representing the applicable worksheet (you want to protect while allowing the user to select locked cells).

Consider explicitly including the following references to create a fully qualified object reference returning the applicable Worksheet object:

A reference to the applicable workbook. The following VBA constructs (among others) may return a Workbook object:
- The Application.ThisWorkbook property.
- The Application.Workbooks and Workbooks.Item properties.
- The Application.ActiveWorkbook property.
A reference to the applicable worksheet. The following VBA constructs (among others) may return a Worksheet object:
- The Workbook.Sheets and Sheets.Item properties.
- The Workbook.Worksheets and Worksheets.Item properties.
- The Application.ActiveSheet property.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/protect-select-locked-cells/
WorkbookObjectReference.WorksheetObjectReference

The protect sheet allow select locked cells VBA code template/structure you learn in this Tutorial assumes the sheet you want to protect (while allowing the user to select locked cells) is (currently) unprotected.

The statements you create in steps #2 to #4 (below) work with the Worksheet object you refer to in this step #1. Consider setting up a With… End With block:

Referring to the Worksheet object (you refer to in this step #1).
To wrap the statements (from steps #2 to #4).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/protect-select-locked-cells/
With WorkbookObjectReference.WorksheetObjectReference
    'StatementsFromSteps2To4
End With

Step 1 Example

Refer to the worksheet named “Protect Select Locked Cells” inside the workbook where the procedure is stored.
Work with the following VBA constructs to obtain a Worksheet object representing this worksheet:
- The Application.ThisWorkbook property: ThisWorkbook.
- The Workbook.Worksheets and Worksheets.Item properties: Worksheets(“Protect Select Locked Cells”).
Add the opening and closing statements for a With… End With block that refers to the worksheet I work with.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/protect-select-locked-cells/
With ThisWorkbook.Worksheets("Protect Select Locked Cells")
    'StatementsFromSteps2To4
End With

Step 2: Lock Cells

Worksheet cells may be:

Locked (the default); or
Unlocked.

Do the following to lock cells (or ensure cells are locked) in the sheet you protect (while allowing the user to select those locked cells):

Refer to the applicable cells (you want to lock).
Lock the cells (you refer to).

Do the following to achieve this:

(1) Start with the With… End With block you created in step #1.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/protect-select-locked-cells/
With WorkbookObjectReference.WorksheetObjectReference
    'StatementsFromSteps2To4
End With

(2) Create a VBA expression that returns a Range object representing the cells you want to lock. The following VBA constructs (among others) may return a Range object:

The Worksheet.Range property.
The Worksheet.Cells and Range.Item properties.
The Worksheet.Rows property.
The Worksheet.Columns property.
The Range.Range property.
The Range.Cells and Range.Item properties.
The Range.Rows property.
The Range.Columns property.
The Range.EntireRow property.
The Range.EntireColumn property.
The Range.Offset property.
The Range.CurrentRegion property.
The Range.End property.
The Range.Resize property.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/protect-select-locked-cells/
With WorkbookObjectReference.WorksheetObjectReference
    .RangeObjectReference
End With

(3) Set the Range.Locked property to True.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/protect-select-locked-cells/
With WorkbookObjectReference.WorksheetObjectReference
    .RangeObjectReference.Locked = True
End With

Step 2 Example

I refer to all cells in the “Protect Select Locked Cells” worksheet in the workbook where the procedure is stored. I work with the Worksheet.Cells property to obtain a Range object representing this cell range.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/protect-select-locked-cells/
With ThisWorkbook.Worksheets("Protect Select Locked Cells")
    .Cells.Locked = True
End With

Step 3: Protect the Sheet

Do the following to protect the sheet.

(1) Start with the With… End With block you developed in steps #1 and #2.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/protect-select-locked-cells/
With WorkbookObjectReference.WorksheetObjectReference
    .RangeObjectReference.Locked = True
End With

(2) Call the Worksheet.Protect method.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/protect-select-locked-cells/
With WorkbookObjectReference.WorksheetObjectReference
    .RangeObjectReference.Locked = True
    .Protect
End With

Step 3 Example

Considering the With… End With block I developed in steps #1 and #2.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/protect-select-locked-cells/
With ThisWorkbook.Worksheets("Protect Select Locked Cells")
    .Cells.Locked = True
    .Protect
End With

Step 4: Allow the User to Select Locked Cells

Do the following to allow the user to select locked cells in the sheet (you protected).

(1) Start with the With… End With block you developed in steps #1 to #3.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/protect-select-locked-cells/
With WorkbookObjectReference.WorksheetObjectReference
    .RangeObjectReference.Locked = True
    .Protect
End With

(2) Set the Worksheet.EnableSelection property to the xlNoRestrictions built-in constant.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/protect-select-locked-cells/
With WorkbookObjectReference.WorksheetObjectReference
    .RangeObjectReference.Locked = True
    .Protect
    .EnableSelection = xlNoRestrictions
End With

Step 4 Example

Considering the With… End With block I developed in steps #1 to #3.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/protect-select-locked-cells/
With ThisWorkbook.Worksheets("Protect Select Locked Cells")
    .Cells.Locked = True
    .Protect
    .EnableSelection = xlNoRestrictions
End With

The full VBA protect sheet allow select locked cells example macro is as follows:

Sub ProtectSheetAllowSelectLockedCells()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/protect-select-locked-cells/
    
    'Step 1: Refer to the "Protect Select Locked Cells" worksheet in this workbook
    With ThisWorkbook.Worksheets("Protect Select Locked Cells")
        
        'Step 2: Lock all cells in the worksheet
        .Cells.Locked = True
        
        'Step 3: Protect the worksheet
        .Protect
        
        'Step 4: Allow any cell (including locked cells) in the worksheet to be selected
        .EnableSelection = xlNoRestrictions
        
    End With

End Sub

The GIF below illustrates the effects of using the VBA protect sheet allow select locked cells example macro.

Notice how:

The Protect Sheet button (inside the Protect group of commands) in the Excel Ribbon (indicating the example worksheet is (currently) unprotected);
Is replaced by the Unprotect Sheet button (indicating the example worksheet is now protected) when I execute the VBA protect sheet allow select locked cells example macro.

Example: Protect Excel sheet allow select locked cells with VBA macros

Download the VBA Protect Sheet Allow Select Locked Cells Example Workbook

This Excel VBA Protect Sheet Allow Select Locked Cells Tutorial is accompanied by an Excel workbook with the data and VBA code I use when describing the step-by-step process above. Get this example workbook (for free) by clicking the button below.

The following Excel Macro and VBA Tutorials may help you better understand and implement the contents above.

Tutorials about general macro and VBA constructs and structures:
- Tutorials for Beginners:
  - Excel Macros: Click here to open.
  - Excel VBA: Click here to open.
- Enable macros in Excel: Click here to open.
- Work with the Visual Basic Editor (VBE): Click here to open.
- Create Sub procedures: Click here to open.
- Refer to objects (click here to open), including:
  - Sheets: Click here to open.
  - Cell ranges: Click here to open.
- Work with:
  - Properties: Click here to open.
  - Methods: Click here to open.
Tutorials with practical VBA applications and macro examples:
- Activate workbook: Click here to open.
- Create new workbook: Click here to open.
- Open workbook: Click here to open.
- Delete sheet: Click here to open.

This Excel VBA Protect Sheet Allow Select Locked Cells Tutorial is part of a more comprehensive series of Excel VBA Protect or Unprotect Sheet Tutorials.

Excel VBA Protect Sheet Without Password in 2 Easy Steps: Click here to open.
Excel VBA Unprotect Sheet Without Password in 2 Easy Steps: Click here to open.
Excel VBA Unprotect Sheet with Password in 2 Easy Steps: Click here to open.
Excel VBA Protect Sheet with Password in 2 Easy Steps: Click here to open.
Excel VBA Protect Sheet Allow Filter in 2 Easy Steps: Click here to open.

You can find more Excel and VBA Tutorials in the organized Tutorials Archive: Click here to visit the Archives.

If you want to learn how to automate Excel (and save time) by working with macros and VBA, you may be interested in the following Premium Excel Macro and VBA Training Materials:

Premium Courses at the Power Spreadsheets Academy: Click here to open.
Books at the Power Spreadsheets Library: Click here to open.
VBA Cheat Sheets: Click here to open.

Code generators.
An extensive code library.
The ability to create your own code library.
Advanced coding tools.