While doing multiple cell copy paste the Selection.PasteSpecial overrides the protected formula cell

[Sameet]

I am using custom copy paste function in my Excel VBA. I trap the key
strokes for paste key and use Selection.PasteSpecial
Paste:=xlPasteValues, Operation:=xlPasteSpecialOperationNone,
SkipBlanks:=False, Transpose:=False function for copy paste. I have
some formula cells on my excel which are protected. If I try to paste
on these formula cells individually (single cell) it does not paste
anything since they are protected.But when I try to paste onto
multiple cells it over writes the values on to my formula cells there
by losing the formulas.

 

[Bob Bridges]

Let me rephrase this and see whether I'm understanding it right: You have a
macro already written which intercepts one of the paste key sequences -
<Ctl-V> or <Shift-Ins>, probably - and in the macro you use the
Selection.PasteSpecial method instead. When the Selection range is just one
cell, protection steps in and prevents the paste; but when multiple cells are
protected, formulae in protected cells are being overwritten. You want some
way of preventing that from happening. Is that right?

If so, I don't know much about protection - I hardly ever use it - so
someone else may be able to tell you something simpler. But if this is a bug
- I mean, a feature of Excel, then you should maybe write into your macro
something that refuses to do the paste if the Selection consists of more than
one cell, like this:

If Selection.Count = 1 Then
Selection.Paste ....
Else
MsgBox "Paste to multiple cells not allowed in this worksheet!", 16, _
"Not allowed!")
End If

If you want to get fancy, you can check all the cells in the Selection and
allow the paste only if none of them are protected.