How to force a paste of values only?
 

Hello -

I was wondering if there is a way in an Excel macro where I can catch
the paste event to make sure the user only copies/pastes the values of
a field and not the format?

I have some conditional formatting and if the user copies a certain
cell, things get screwed up. Is there also a way to block a cut/paste?

I tried relative formatting but for some reason things get screwed up
when I run a macro that creates the relative formatting:

With lTPSheet.Range("B" & lSelection.Row & ":E" & lSelection.Row)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=$D" &
lSelection.Row & "=""X"""
.FormatConditions(1).Font.ColorIndex = 2
.FormatConditions(1).Interior.ColorIndex = 3
End With

The Formula1:="=$D" & lSelection.Row & "=""X""" does NOT set the
correct row for some reason (usually off by 3) if it is a relative
reference rather than absolute ($).

Very strange unless I'm really missing something ...

Thanks!
Joe

How to force a paste of values only?
 

I ran into something similiar to this with needing to make sure certain cells
weren't changed. Every time a user selected a cell I copied the values and
formats etc to a hidden cell. and whenever they changed a cell i compared the
hidden cell's values and formats to the one the user changed, if I didn't
like the changes I copied the hidden cell to the user changed one.

"Joe HM" wrote:

Hello -

I was wondering if there is a way in an Excel macro where I can catch
the paste event to make sure the user only copies/pastes the values of
a field and not the format?

I have some conditional formatting and if the user copies a certain
cell, things get screwed up. Is there also a way to block a cut/paste?

I tried relative formatting but for some reason things get screwed up
when I run a macro that creates the relative formatting:

With lTPSheet.Range("B" & lSelection.Row & ":E" & lSelection.Row)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=$D" &
lSelection.Row & "=""X"""
.FormatConditions(1).Font.ColorIndex = 2
.FormatConditions(1).Interior.ColorIndex = 3
End With

The Formula1:="=$D" & lSelection.Row & "=""X""" does NOT set the
correct row for some reason (usually off by 3) if it is a relative
reference rather than absolute ($).

Very strange unless I'm really missing something ...

Thanks!
Joe

How to force a paste of values only?
 

Hello -

Thanks for the feedback! That sounds like a good idea ...

Joe

How to force a paste of values only?
 

Ben,

Did you do this with an event macro? Would you mind posting your cod
here? I'm trying to do something similar, but am kinda new to VB an
really struggling. I'd be greatful to see how you accomplished this.

Thanks in advance

--
Cel
-----------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...fo&userid=1941
View this thread: http://www.excelforum.com/showthread.php?threadid=35436