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

How about Paste Special with Value or Formula?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

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

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

Pete

On Dec 10, 8:28 pm, juanpablo
wrote:
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

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

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

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

On Mon, 10 Dec 2007 12:28:03 -0800, juanpablo
wrote:

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

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

"Gord Dibben" wrote:

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

On Mon, 10 Dec 2007 12:28:03 -0800, juanpablo
wrote:

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

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" wrote:

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

On Mon, 10 Dec 2007 12:28:03 -0800, juanpablo
wrote:

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

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


On Mon, 10 Dec 2007 15:06:03 -0800, juanpablo
wrote:

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

"Gord Dibben" wrote:

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

On Mon, 10 Dec 2007 12:28:03 -0800, juanpablo
wrote:

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

Works for me with drag/copy.


Gord

On Mon, 10 Dec 2007 15:18:00 -0800, juanpablo
wrote:

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" wrote:

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

On Mon, 10 Dec 2007 12:28:03 -0800, juanpablo
wrote:

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