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_...ame=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?

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

"ewan7279" wrote in message
...
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_...ame=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?

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.

"Zone" wrote:

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

"ewan7279" wrote in message
...
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_...ame=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?

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

"ewan7279" wrote:

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.

"Zone" wrote:

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

"ewan7279" wrote in message
...
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_...ame=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?

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

"ewan7279" wrote in message
...
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...

"ewan7279" wrote:

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.

"Zone" wrote:

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

"ewan7279" wrote in message
...
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_...ame=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?

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" wrote:

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

"ewan7279" wrote in message
...
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...

"ewan7279" wrote:

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.

"Zone" wrote:

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

"ewan7279" wrote in message
...
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_...ame=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?