INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Protecting used cells(merged) in an excel spreadsheet

Protecting used cells(merged) in an excel spreadsheet

(OP)
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

RE: Protecting used cells(merged) in an excel spreadsheet

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Protecting used cells(merged) in an excel spreadsheet

(OP)
Hi Skip

The code I tried was

CODE

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.

RE: Protecting used cells(merged) in an excel spreadsheet

CODE

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Protecting used cells(merged) in an excel spreadsheet

A year or so ago I struck a problem with unlocked merged cells on a protected worksheet.  See thread707-1762745: Deleting an unlocked merged cell on a protected worksheet.  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.

RE: Protecting used cells(merged) in an excel spreadsheet

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

RE: Protecting used cells(merged) in an excel spreadsheet

Hey! I'm retired now. Don't get me chained down to no 9 to 5! winky smile

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close