How to force a paste of values only?

  • Thread starter Thread starter Joe HM
  • Start date Start date
J

Joe HM

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