Restrict user's ability to paste into cells

[Guest]

Hi All,

I would like to restrict what a user can paste into a cell - values only. I
found a response to another question
(http://p2p.wrox.com/topic.asp?TOPIC...qe9I4A8AAAC5x38E&bcsi_scan_filename=topic.asp),
but it only works by changing the ctrl+v to a paste special macro shortcut -
the rest of the code does not seem to work.

Any ideas please?

 

[Zone]

If you want the user to only be able to enter values into the entire
worksheet, then this should work. Copy this code, right-click on the sheet
tab, select View Code, and paste the code in there. HTH, James

Private Sub Worksheet_Change(ByVal Target As Range)
If Left(Target.Formula, 1) = "=" Then
Target.ClearContents
Target.Select
MsgBox "Please enter values only.", vbExclamation
End If
End Sub

 

[Guest]

Hi Zone,

Thanks for this bit of code - it will stop the user from being able to copy
and paste formulas into the sheet, or entering formulas of their own within
the sheet.

However, I also want to stop them from pasting in cells that could change
the format of those in the sheet, as they currently contains conditional
formatting. With your solution, the user is still able to 'PasteAll',
damaging the existing formatting.

Any ideas how to get round this, or why the solution on the website I pasted
into my original post worked for another user but not me?

Thanks.

 

[Guest]

Hi Zone,

The code you provided now interferes with another piece of code I have to
update certain cells in the sheet unfortunately.

I think the solution must only lie in the paste property somehow...

I'll keep trying...

 

[Zone]

Hi ewan. Sorry my solution didn't work for you. I thought you would have
the sheet protected, since sheet protection offers several options to
protect formatting. Maybe that's not feasible in your situation, since you
seem to want the users to be able to do some cutting and pasting but not
some. BTW, if my solution interferes with one of your procedures, you can
temporarily disable it like this:
application.enableevents=false
'do your stuff
application.enableevents=true
and of course you can temporarily disable sheet protection and re-enable it,
as well, James

 

[Guest]

Hi James,

In Brief:

The worsksheet is protected, with certain cells unprotected for the user to
enter data. There are many users at many locations who all have different
ways to produce their data and they do this in separate worksheets. They are
likely to copy and paste the results of this work into the sheet that I
provide them.

My sheet contains conditional formatting (yellow if blank) to show cells
that require data input. There is also a 'traffic light' formatting (green,
amber, red) to show the severity of error rates.

When the user copies and pastes data into my sheet, they can 'paste over'
this formatting, even though the sheet is protected, because the cells into
which they are entering data are unlocked.

With your code and enableevents, plus the ctrl+V shortcut for a Paste Values
macro, I am half way to what I need. What I finally need is a way to disable
the right-click Paste, Edit=>Paste and Menu Bar Paste button. Is there a way
to do this?

Do you think a suggestion for levels of protection would be a good idea? It
would solve the problem in my case if I could lock the cell to
'Values/Numbers only' or something similar before protecting the sheet...

 

[Zone]

ewan, I don't know how to disable the paste functions you mentioned other
than to protect the sheet using the default protection options, which would
disable all pasting. Then your ctrl-v routine could unprotect the sheet,
paste the values and reprotect the sheet. The only drawback to this is the
users wouldn't be able to just type into a cell. If that's a problem, you
might try posting a new message for help on disabling those specific pasting
methods. HTH, James

 

[Guest]

Hi James, check this: http://www.vbaexpress.com/kb/getarticle.php?kb_id=373

 

[Zone]

Interesting, ewan. Thanks for turning me on to that. Cheers, James