PDA

View Full Version : Solved: Solved....Locking Cells?



davisbrc
01-30-2009, 09:04 AM
I am a VB noob, so I am wondering what needs to be changed in this code to allow me to lock all the cells in every sheet, except for cells that are yellow.

Dim wSht As Worksheet
Dim allwShts As Sheets
Set allwShts = Worksheets
On Error Resume Next
For I = 1 To Sheets.Count
Sheets(I).Activate
ActiveSheet.Unprotect Password:=""
Application.ScreenUpdating = False
Cells.Select
Selection.Locked = False
Selection.FormulaHidden = False
ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants).Select
Selection.Locked = True
Selection.FormulaHidden = True
ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas).Select
Selection.Locked = True
Selection.FormulaHidden = True
ActiveSheet.Protect Password:=""
Application.ScreenUpdating = True
Next I

lucas
01-30-2009, 09:49 AM
Try this:
Option Explicit
Sub ColLock()
Dim Cel As Range, c As Range, Col As Long, Lkd As Boolean
Dim sh As Worksheet
Set Cel = Range("A1")
Application.ScreenUpdating = False
Lkd = Cel.Locked
For Each sh In Worksheets
sh.Unprotect
For Each c In sh.UsedRange
If c.Interior.ColorIndex = 6 Then
c.Locked = Not Lkd
End If
Next
Next
For Each sh In Worksheets
sh.Protect
Next
Application.ScreenUpdating = True
End Sub

example attached

mdmackillop
01-30-2009, 10:22 AM
Why not create a Style for fill colour and "not locked". Saves the problem arising in the future.

davisbrc
01-30-2009, 11:06 AM
Thanks Lucas and MD for the quick responses.

I used the code Lucas provided and it worked in my spreadsheet after I got rid of a couple merged cells, which gave me a runtime error code of '1004'

Thanks for your help.

lucas
01-30-2009, 11:35 AM
I suggest you avoid merged cells at all costs if possible, this is a perfect example of why. Use center across selection instead.

lucas
01-30-2009, 11:36 AM
Be sure to mark your thread solved if you have your solution. Use the thread tools at the top of the page.