Cell Locking and Protection

**xwishmasterx** · 01-17-2010, 05:32 AM

I am really puzzled by the way the protection works in excel..it seems almost to stupid..

Well, my problem is I have some cells that should NOT be locked or protected in any way (user may delete / edit / merge...etc).

The cells have been formatted with NO locking, but as soon as the sheet is protected I am not allowed to delete?!

also, when I unprotect the sheet and check the cells locking is back on?!

Can anyone explain why? Or have the people at MS screwed up again?

**teylyn** · 01-17-2010, 05:38 AM

Hi,

before you go Tools - Protection - Protect sheet ....

- click each cell you want to be able to edit
- open the formatting dialog and on the Protection tab unselect the "Locked" check box

Then click Tools - Protection - Protect sheet and select the protection options for the sheet, i.e. whether or not users may select protected cells, etc.

You should now be able to clear unprotected cells and change their values.

You may not delete the cells, i.e. remove them from the sheet and have the surrounding cells fill the gap.

If that is not the behaviour you're getting, please upload a sample sheet in which the problem can be reproduced.

Explain what behaviour you would expect .

cheers

**xwishmasterx** · 01-17-2010, 06:18 AM

well, the problem I am facing is bound to the fact that "merging" cells is disabled when the sheet is protected. I have a macro that creates "boxes" by merging cells. I can create these boxes, but not "un-merge" them or move them ( as this will require other cells to be merged.)

Is there a way to have "merging" enabled on a protected sheet?

**teylyn** · 01-17-2010, 06:20 AM

rule 1: avoid merged cells
rule 2: if you can't avoid merged cells, merge them and live with the consequences. If you don't like the consequences, see rule 1

**royUK** · 01-17-2010, 06:22 AM

Firstly, I would recommend that you avoid using merged cells for anything. They are nearly always problematical.

You probably need to unprotect the sheet at the start of your code

Sheet1.Unprotect "your password"
'code
Sheet1.protect "your password"

**xwishmasterx** · 01-17-2010, 06:32 AM

teylyn and roy; I can see merging cells isn't to smart to work with. However, it works really well for what I am doing. The only thing that stands in my way is the problem of merge / un-merge on a protected sheet. I wonder why this option is disabled. It just doesn't make any sense...

To explain further, the macro I'm using is creates theses blocks by merging cells. However I do not want the possibility to create a block where there are already some content.

Maybe it is possible to only allow this macro to create the boxes in a certain area?

heres the code for creating the merged boxes:

Option Explicit
Dim Col As Integer, Rw As Integer, Ac As Integer, Tx As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 Then
    If Rw = 0 And Target.Column = 1 Then
        Col = Target.Interior.ColorIndex
        Rw = Target.Value
        Ac = Target.Offset(, 1).Value
        Tx = Target.Offset(, 2).Value
    ElseIf Rw > 0 Then
        If Target.Row >= Rw Then
            Target = Tx
            With Target.Offset(-Rw + 1).Resize(Rw, Ac)
                .Interior.ColorIndex = Col
                .BorderAround ColorIndex:=1, Weight:=xlThick
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .WrapText = True
                .MergeCells = True
            End With
            Rw = 0
        End If
    End If
End If

End Sub

**royUK** · 01-17-2010, 06:58 AM

How can Rw = 0?

What determines when not to merge cells?

Have you tried the suggestion about unprotecting?

It might help to attach a workbook

**xwishmasterx** · 01-17-2010, 08:59 AM

I have come to the decision not to use merged cells .XD It seems I have gotten it to work and I can accept the result. thanks for your support

**royUK** · 01-17-2010, 09:13 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save