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?

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

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

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


"Zone" wrote:

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

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

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

"ewan7279" wrote in message
...
Hi James, check this:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=373


"Zone" wrote:

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

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