Excel

Unprotect all colored cells only

Ease of Use

Easy

Version tested with

2003 

Submitted by:

Ken Puls

Description:

This procedure was written to quickly ensure that all light green cells are unlocked, and that all other cells are locked, so that the green cells (intended for data entry) are available when sheet protection is turned on. 

Discussion:

When developing Excel spreadsheets for other users, it is a best practice to shade all cells that you want your users to enter data into. This allows your users to know exactly where they should (and shouldn't) enter data. This procedure allows you to quickly ensure that all shaded cells are unlocked, and all unshaded cells are locked on each sheet in the workbook. Once sheet protection is applied to your worksheets, users will only be able to enter data in the shaded cells. This macro is coded to unprotect only light green cells, but the sample file also includes a utility to evaluate any cell color so that you can replace it with the color (colour) you prefer. PLEASE NOTE: The intention of this entry was to be used by the spreadsheet developer, and not the end user. Because of this, it was assumed that the developer would know that all of their sheets were uprotected, run this macro, then protect the appropriate sheets. If you choose to use this in a user's setting, the author would highly recommend testing all sheets for sheet protection prior to running the macro as no error handling exists in this routine. A KB entry to do this can be found at http://www.vbaexpress.com/kb/getarticle.php?kb_id=551. 

Code:

instructions for use

			

Option Explicit Sub UnprotectGreenCells() 'Macro purpose: To unlock all green cells 'NOTE: All worksheets in the workbook must be ' unprotected before this procedure is run Dim cl As Range, ws As Worksheet, lColor As Long 'Set the cell color that you want to protect lColor = 35 'green 'It may be a good idea to test all sheets to see if any are protected 'here. One method to do this would be to test the function found here 'http://www.vbaexpress.com/kb/getarticle.php?kb_id=551 'Cycle through each worksheet in the workbook For Each ws In ActiveWorkbook.Worksheets For Each cl In ws.UsedRange 'Change colored cell to unlocked, and 'all other cells to locked If cl.Interior.ColorIndex = lColor Then cl.Locked = False Else cl.Locked = True End If Next cl Next ws End Sub

How to use:

  1. Copy above code.
  2. In Excel press Alt + F11 to enter the VBE.
  3. Press Ctrl + R to show the Project Explorer.
  4. Right-click desired file on left (in bold).
  5. Choose Insert -> Module.
  6. Paste code into the right pane.
  7. Press Alt + Q to close the VBE.
  8. Save workbook before any other changes.
 

Test the code:

  1. Color some sections of the worksheet(s) light green.
  2. Protect the sheet(s), and try to enter data in any cell. (It shouldn't let you.)
  3. Unprotect the sheet(s)
  4. Press Alt + F8 and choose "UnprotectGreenCells".
  5. Protect the sheet(s).
  6. Try entering data in any cells. (You should be able to enter data in green cells only.)
 

Sample File:

UnprotectGreen.zip 7.9KB 

Approved by mdmackillop


This entry has been viewed 277 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express