How to force a paste of values only?

[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

[Leith Ross]

Hello Joe,

Why not protect the sheet and unlock the cells the user can have access to?

Just a suggestion,
Leith Ross

[ben]

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

[Joe HM]

Hello -

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

Joe

[Celt]

Ben,

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

Thanks in advance!