VBA & Macro Problems

Prevent Excel VBA Formatting from Locking a Cell

Chanuka GeekiyanageChanuka Geekiyanage Nov 14, 2026 868 views

Question details

Keep cell C3 unlocked when a VBA macro changes its formatting.

Prevent Excel VBA Formatting from Locking a Cell
Product
Microsoft Excel VBA
Device & OS
not provided
Scenario
A macro formats cell C3, which needs to remain editable when the worksheet is protected.
Observed behavior
ClearFormats resets the cell's Locked property, so a later macro run fails after the worksheet is protected.
Before you start

Open the workbook in Excel and identify the worksheet containing C3. If the sheet is protected, use Review > Unprotect Sheet and enter its password if prompted before changing the cell's protection or formatting.

Solution 1Recommended

Format C3 directly without using ClearFormats

Use this approach when you know which formatting properties the macro needs to change and want C3 to remain unlocked.

1
Open the VBA editor

In Excel, press Alt+F11, open the relevant module, and locate the macro that formats C3.

2
Replace the formatting code

Replace the Select, Selection, and ClearFormats statements with direct references. For example, adjust these properties to suit your worksheet: With ActiveSheet.Range("C3") .NumberFormat = "General" .HorizontalAlignment = xlGeneral .Font.Bold = False .Borders.LineStyle = xlNone .Locked = False End With

3
Run and protect the sheet

With the target worksheet active and unprotected, run the macro from Developer > Macros. Then select Review > Protect Sheet and test whether C3 remains editable.

Format C3 directly without using ClearFormats
Running the macro again: The example sets Locked and should be run while the worksheet is unprotected. Unprotect the sheet before later runs, or modify the macro to handle protection with the appropriate password and settings.
Free Microsoft Office alternative

Work with Excel spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for common Excel formats, making it easier to move everyday spreadsheet work between applications. It does not fix Excel's VBA behavior; check macro support in your WPS version before migrating VBA-dependent workbooks.

Open and edit common Microsoft Excel formats, including XLS and XLSXUse a familiar spreadsheet interface for everyday cell formattingMove routine spreadsheet work to a lightweight office suiteGet started with a free Office alternative
microsoft office alternative - wps office

Frequently Asked Questions

Why does ClearFormats lock C3?

ClearFormats restores default formatting, including the cell's Locked property. The property takes effect when the worksheet is protected.

How can I check whether C3 is unlocked?

Select C3, open Format Cells, and check the Protection tab. The Locked checkbox must be cleared for the cell to remain editable under worksheet protection.

Can I run the example macro on a protected worksheet?

The example sets the Locked property, so unprotect the worksheet through Review > Unprotect Sheet before running it. Protect the sheet again afterward.

Do I need to select C3 before formatting it with VBA?

No. Use a direct reference such as ActiveSheet.Range("C3") in a With block. Make sure the intended worksheet is active, or replace ActiveSheet with an explicit worksheet reference.