CODEWITHSUNDEEP
vbaexcelformatting
Marco
Marco

Reputation: 508

Protected worksheet allows editing cell format by copy and paste

I have a worksheet which is protected. Only some cells are editable and the user can write into them but cannot change the cell format as usual. If he decides to copy and paste data from another worksheet to mine then the cell formatting of the other worksheet is applied to my cells. I want my cells to be editable in value but their cell format must not be editable at all! How can I do that?

Thanks in advance!

Marco

Upvotes: 4

Views: 9522

Answers (2)

MLDev
MLDev

Reputation: 1277

One method would be using the worksheet_change event to see if any of the cells have changed:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range("J2").Address Then
        'your code
    End If
End Sub

Next apply the original formatting to the cells that have changed.

Upvotes: 1

Marco
Marco

Reputation: 508

I used this in order to only paste the values if the user decides to copy and paste in the cells whose format is protected:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    If Application.CutCopyMode = xlCopy Then

        Application.EnableEvents = False

        Application.Undo
        Target.PasteSpecial Paste:=xlPasteValues

        Application.EnableEvents = True

    End If

End Sub

It undoes any pastes into the worksheet and pastes it again (only values, no formatting).

Upvotes: 4

Related Questions