Formats

[juanpablo]

Is it possible to block all formats, so every time I modified the value
inside the cell, it does not change the format.
For example, if I copy a cell with borders, but I want to only copy de value
and not the format, but using the mouse, not using the CTRL+C or CTRL+V
So I want to be able to replace the contents only using the mouse, and not
the paste special function, keeping the formats.

JPG

 

[Bernard Liengme]

How about Paste Special with Value or Formula?
best wishes

 

[Pete_UK]

What's wrong with Paste Special | Values ? You select those options by
means of the mouse !

Pete

 

[Bernard Liengme]

Or in XL 2003, you can have the Paste Option icon appear next to the cell
One of the choices in it is: Format to Match Destination
best wishes

 

[Gord Dibben]

Paste Special>Values would do the trick as others have mentioned.

If you don't want to take the extra couple of mouse-clicks you can use event
code behind the sheet.

Copy and paste only........

Private Sub Worksheet_Change(ByVal Target As Range)
'retain formatting when a cell is pasted over
On Error GoTo endit
Dim myValue
With Application
.EnableEvents = False
myValue = Target.Value
.Undo
Target = myValue
End With
endit:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code"

Copy/paste the above into that sheet module.


Gord Dibben MS Excel MVP

 

[juanpablo]

Ok I see, but I still dont understand this macro, what I want is:
If I drag the content, then copy just the value, not the format.

You macro does the opposite.

Thanks.

JPG

 

[juanpablo]

Ahhh OK now I understand, it works with copy and paste, great!!
I was just wondering if it was possible to make it with just draging.

JPG

 

[Gord Dibben]

That's what it does.

Have you tried it?

You can copy and paste or drag/copy and formats won't be copied.

Cannot be used for cut and paste.


Gord

 

[Gord Dibben]

Works for me with drag/copy.


Gord

Ahhh OK now I understand, it works with copy and paste, great!!
I was just wondering if it was possible to make it with just draging.

JPG