Changing the locked propery of merged cells.

[Guest]

Apparently you can't, at least every way I've tried.

What I have is a bunch of sheets with some cells that reference cells on
other sheets (for instance, on Sheet1 you might have in D26 the formula
"=Sheet2!B5"), I've already got a macro to make all these cells italic. I did
this just so the user can see what he's not supposed to edit, but I'd like to
take it a step farther and have it actually lock all the linked cells. What I
wrote works, but crashed every time it hits a merged cell. Any suggestions?

 

[keepITcool]

this seems to work (if sheet has no password)
if protects, hides and "grays" all formulas...


Sub LockM()
Dim c As Range, s As Range, a As Range
'Changing protection is for demo only.
ActiveWorkbook.Colors(15) = RGB(234, 234, 234)
ActiveSheet.Unprotect
ActiveSheet.Protect userinterfaceonly:=True
For Each c In ActiveSheet.UsedRange.Columns
On Error Resume Next
Set s = c.SpecialCells(xlFormulas)
On Error GoTo 0
If Not s Is Nothing Then
For Each a In s.Areas
a.Cells.Locked = True
a.Cells.FormulaHidden = True
a.cells.Interior.colorindex=15
Next
End If
Next



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >


"=?Utf-8?B?VGhlIFdvbmRlciBUaGluZw==?="