Protected worksheet allows editing cell format by copy and paste

Question

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

I think a better way is to use a `Worksheet_Change` event and re-apply the formatting, though this really depends on the approach. Forcing to paste values when `Copy`ing is easy, but what if they used `Cut`? However, if you still want to go down this path, a good resource is [this](http://www.mrexcel.com/forum/excel-questions/56674-force-paste-special-values.html). — WGS, Feb 18 '14 at 14:36
Although the user won't be able to cut and paste in my worksheet anymore this is still a viable solution for me because I really do not want them to be able to change the cell format. Thanks! — Marco, Feb 18 '14 at 14:52

score 4 · Accepted Answer · answered Feb 19 '14 at 14:30

4

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).

answered Feb 19 '14 at 14:30

Marco

508
2
7
22

Hi @Marco, I know this is a old thread, but I have a question... I got a very similar problem to yours. The only difference is, that I need the sub to work, even if it is a copy paste process between two excel instances. Most of my colleagues use two screens with two instances and this sadly causes a problem with your solution. If you would like to help me, it would be very nice of you to contribute to the answering of [my question](https://stackoverflow.com/questions/58288693/how-to-let-users-paste-cells-from-another-excel-instance-ctrlv-but-paste). – RaspiManu Oct 17 '19 at 06:51

score 1 · Answer 2 · edited Feb 21 '14 at 21:33

1

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.

edited Feb 21 '14 at 21:33

Andrew Barber

39,603
20
94
123

answered Feb 18 '14 at 16:07

Math4123

1,267
4
12
23

When I use this, I cannot undo my changes. Is there a way that I can still undo changes in the Range? – Marco Feb 21 '14 at 07:37
what do you mean by undo changes? – Math4123 Feb 21 '14 at 07:38
1

to revert what I typed in / copied in. My standard shortcut for that is CTRL + Z – Marco Feb 21 '14 at 07:43
Its the same when you press ctrl+z the event is triggered again. You would need to figure what has been changed and apply the changes. If you are still having trouble feel free to email me your files and data so I can have a look at them – Math4123 Feb 21 '14 at 07:45
I just sent it to you. Thanks for your help! – Marco Feb 21 '14 at 08:03

Protected worksheet allows editing cell format by copy and paste

2 Answers2

Linked