PDA

View Full Version : Locking cells in excel



jerryhouston
02-04-2007, 08:33 AM
I have a spreadsheet with over 15 tabs that I use to gather budget data. But all the tabs have data in different places. Is there a way to lock cells based on a cell color? I know how to lock cells individually, but it takes so long to do each spreadsheet differently.

Thanks

Bob Phillips
02-04-2007, 08:58 AM
The only way that you could get the coloured cells would be individually, so that would be no better.

mdmackillop
02-04-2007, 10:03 AM
It's a quiet afternoon, so here's a way do deal with a selection of colours.

jerryhouston
02-04-2007, 10:09 AM
How about if I want to only have cells with a specific color unlocked and everything else locked?

Thanks

mdmackillop
02-04-2007, 10:23 AM
A slight change is required to my earlier code
Sub ColLock()
Dim Cel As Range, c As Range, Col As Long, Lkd As Boolean
Dim sh As Worksheet

Set Cel = ActiveCell
If Cel.Interior.ColorIndex = xlNone Then
MsgBox "Select coloured cell"
Exit Sub
End If
Application.ScreenUpdating = False
Lkd = Cel.Locked
Col = Cel.Interior.ColorIndex
For Each sh In Worksheets
sh.Unprotect
For Each c In sh.UsedRange
If c.Interior.ColorIndex = Col Then
c.Locked = Not Lkd
End If
Next
Next

If Lkd = False Then
Cel.Offset(, 1) = "Locked"
Else
Cel.Offset(, 1) = "Open"
End If

For Each sh In Worksheets
sh.Protect
Next

Application.ScreenUpdating = True
End Sub


To lock all cells
Sub LockAll()
Dim sh As Worksheet
For Each sh In Worksheets
sh.Unprotect
sh.Cells.Locked = True
sh.Protect
Next
With Sheets(1)
.Unprotect
.Range("B1:B20") = "Locked"
.Protect
End With
End Sub

Bob Phillips
02-04-2007, 11:19 AM
Which is doing it individually, whereas I thought the OP said he knew how to do that?

mdmackillop
02-04-2007, 11:29 AM
I read it as doing it manually, hence my response. The OP can let us know!

jerryhouston
02-04-2007, 12:20 PM
Not quite sure I understand. I would like all cells locked (including cells with no shading) except for cells highlighted in green or yellow.

mdmackillop
02-04-2007, 12:29 PM
This example shows one way of accomplising what you seem to be after. I don't know how many colours you want to use, our how you want to trigger the changes.

lucas
02-04-2007, 12:30 PM
Jerry,
you need to select one of the highlighted cells before running Malcolms code. It only works for one(the activecell)color.

jerryhouston
02-04-2007, 12:38 PM
It works, thanks. How can I hide this from others using the template? Is there a way of protecting this sheet, or a password or something else?

Thanks

mdmackillop
02-04-2007, 01:30 PM
Here's a protected version. The button sheet is simply hidden. VBA code and sheet passwords is MDM. This can be changed here.
Option Explicit
Const PW = "MDM" You should be aware that Excel protection is very insecure.

johnske
02-04-2007, 03:15 PM
... You should be aware that Excel protection is very insecure.Indeed, protection is essentially there to protect your project from accidental changes, not to "secure" it. Have a read here (http://xlvba.3.forumer.com/index.php?act=ST&f=8&t=270&st=0#entry371)