Turn off pasting in range of cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to keep people from pasting anything in a group of cells. I have a group of cells(a1:a5) that I don't want people to be able to paste anything in. Any help would be great. Thanks. Matt
 
Matt

lock them (which they should be by default) and protect the sheet.

Regards

Trevor


Matt said:
Is it possible to keep people from pasting anything in a group of cells.
Click to expand...
I have a group of cells(a1:a5) that I don't want people to be able to paste
anything in. Any help would be great. Thanks. Matt
 
Right click on the sheet tab where these cells are located

select view code

At the top of the module, In the left dropdown select worksheet and in the
right dropdown select selectionChange

Put in code like below in that event.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
if Not intersect(target, Range("A1:A5")) is nothing then
Application.cutcopymode = false
msgbox "Please do not try to paste in A1:A5"
End if
End Sub

--
Regards,
Tom Ogilvy


Matt said:
Is it possible to keep people from pasting anything in a group of cells.
Click to expand...
I have a group of cells(a1:a5) that I don't want people to be able to paste
anything in. Any help would be great. Thanks. Matt
 
Another way would be to lock the cells and protect the worksheet
(tools=>Protect=>Worksheet). I assumed you didn't want to do that, but
perhaps you are not aware of sheet and cell protection.

The default property for cells is to be locked. You would need to unlock
the cells you want to allow changes to (format=>Cells, protection tab,
uncheck locked)
--
Regards,
Tom Ogilvy
Matt said:
Is it possible to keep people from pasting anything in a group of cells.
Click to expand...
I have a group of cells(a1:a5) that I don't want people to be able to paste
anything in. Any help would be great. Thanks. Matt
 
Thanks Tom. I didn't want to lock the cells. I have a validation list in each of these cells. For some reason though, Excel will allow you to paste something in these cells even though it is not in the validation lists. Then when you delete the text in the cell it removes the data validation drop down list. Any ideas how to fix this? If not, thanks for the other info, it works. Thanks again. Matt
 
Actually, it is the pasting in the cells that removes the dropdown list.
Validation is treated as formatting, so with a straight paste, the source
formatting is applied (which does not contain the validation).

Locking the cell and protecting the sheet will prevent this and the List
style validation still works.

--
Regards,
Tom Ogilvy


Matt said:
Thanks Tom. I didn't want to lock the cells. I have a validation list in
Click to expand...
each of these cells. For some reason though, Excel will allow you to paste
something in these cells even though it is not in the validation lists.
Then when you delete the text in the cell it removes the data validation
drop down list. Any ideas how to fix this? If not, thanks for the other
info, it works. Thanks again. Matt
 
I have some validations in the cell, for maxlengths etc. But, when
paste the text directly into the cell (without going into the cel
first), the validation is removed. Even when I do paste special a
text, then the validation is not removed, but doesnt work. So, I nee
to disallow pasting into cells directly without going into the cel
first, so that validation remains
 
Back
Top