Protecting used cells(merged) in an excel spreadsheet 1

[EliseFreedman]

Hi There

I have a series of workbooks where I want to protect the data to prevent users altering the data by mistake.

I could go through and protect the relevant cells manually but I was hoping to make life easier for myself and do it using code.

I want to be able to cycle through all the cells and if the cell has a value then the cell should be locked. If it is blank then the cell should be left unlocked.

I have tried various bits of code from the web but with no luck. I think part of the issue may be that Some of the cells in the worksheet are merged cells. Theoretically I could change the sheet so that there are no merged cells but if im going to have to do that then I might as well just protect the cells manually instead)

Can anyone help

 

[SkipVought]

Hi,

Turn on your macro recorded an record changing the protection on ONE CELL from unlocked to locked.

Then turn off your recorder, copy the code and paste it below, unless you feel competent to modify to meet your needs.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[EliseFreedman]

Hi Skip

The code I tried was

 Dim chCell As Range
    Dim chRng As Range

    ActiveSheet.Unprotect
    Set chRng = ActiveSheet.Range("A1:B6")

    'Check cell value in body and lock cells with content
    For Each chCell In chRng.Cells
        chCell.Locked = (chCell.Value <> "")
    Next chCell

    ActiveSheet.Protect

this worked like a dream on a test worksheet until I introduced merged cells. At that point I get a run time error 1004.

 

[SkipVought]

Sub test()
    Dim chCell As Range, cm As Range

    ActiveSheet.Unprotect

    'Check cell value in body and lock cells with content
    For Each chCell In ActiveSheet.UsedRange.Cells
        If chCell.MergeCells Then
            Set cm = chCell.MergeArea
        Else
            Set cm = chCell
        End If
        
        If Len(cm.Cells(1, 1).Value) > 0 Then
            cm.Locked = True
        Else
            cm.Locked = False
        End If
    Next chCell

    ActiveSheet.Protect

End Sub

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Deniall]

A year or so ago I struck a problem with unlocked merged cells on a protected worksheet.[ ] See thread707-1762745.[ ] Aspects of this might be relevant to your problem.[ ] Merged cells have the potential to be troublesome, and should be avoided except as a last resort.

 

[EliseFreedman]

I really wish Mr Skipvought worked in my office. He is so helpful when it comes to resolving my Excel programming issues

 

[SkipVought]

Hey! I'm retired now. Don't get me chained down to no 9 to 5! ;-)

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}