Hi,

I just want to share my copy/paste code with 2 futures:
- default copy one cells value without a carriage-return/line-feed (CR/LF) pressing Ctrl-C
- default paste without formats when pressing Ctrl+V
All this without losing undo/redo

Still want to copy the format? Just click the paste button in the ribbon.

First we need to add 2 buttons that will pass only text. range-text <> html-text
- Click File/Options/"Quick Access Toolbar"
- Select "All Commands" under "Choose commands from:"
- Select "Paste Values" and click "Add"
- Select "Paste and Keep Text Only" and click "Add"
- Click "OK"
If anyone knows how to add these buttons with VBA-code, please share it. Thanks!

Now press "Alt" and find out what number those 2 added buttons have been given and change the "4" and "5" accordingly in following code...

For newbies: While on a excel-sheet press Alt+F11 to open the VBA editor, press Ctrl+R and insert a module pressing Alt+I M and add this code:
Private Enum enumMAPVK          '''used in MapVirtualKey
    MAPVK_VK_TO_VSC = 0
    MAPVK_VSC_TO_VK = 1
    MAPVK_VK_TO_CHAR = 2
    MAPVK_VSC_TO_VK_EX = 3
End Enum
Private Enum enumKBE            '''used in Keybd_event
    KBE_KeyDown = 0
    KBE_KeyUp = 2
    KBE_ExtKeyDown = 1
    KBE_ExtKeyUp = 3
End Enum

#If VBA7 Or Win64 Then     '''Code is running in the new VBA7 editor
    Private Declare PtrSafe Function GetKeyState Lib "user32" (ByVal nVirtKey As Integer) As Integer
        '''Retrieves the status of the specified virtual key whether the key is up, down, or toggled (alternating on/off each time the key is pressed).
    Private Declare PtrSafe Function CharToOem Lib "user32" Alias "CharToOemA" (ByVal lpszSrc As String, ByVal lpszDst As String) As Long
        '''Translates a string into the OEM-defined character set.
    Private Declare PtrSafe Function MapVirtualKey Lib "user32" Alias "MapVirtualKeyA" (ByVal uCode As Long, ByVal uMapType As enumMAPVK) As Long
        '''Translates (maps) a virtual-key code into a scan code or character value, or translates a scan code into a virtual-key code.
    Private Declare PtrSafe Sub Keybd_event Lib "user32" Alias "keybd_event" (ByVal bVk As Byte, ByVal bScan As Byte, ByVal dwFlagsv As enumKBE, ByVal dwExtraInfo As LongPtr)
        '''Synthesizes a keystroke. The system can use such a synthesized keystroke to generate a WM_KEYUP or WM_KEYDOWN message.
    Private Declare PtrSafe Function OemKeyScan Lib "user32" (ByVal wOemChar As Long) As Long
        '''Provides information that allows a program to send OEM text to another program by simulating keyboard input.
    Private Declare PtrSafe Function VkKeyScan Lib "user32" Alias "VkKeyScanA" (ByVal cChar As Byte) As Integer
        '''Translates a character to the corresponding virtual-key code and shift state for the current keyboard.:
#Else                       '''Code is running in VBA version 6 or earlier  !!! NOT TESTED JET !!! Can anyone confirm its working?
    Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Integer) As Integer
        '''Retrieves the status of the specified virtual key whether the key is up, down, or toggled (alternating on/off each time the key is pressed).
    Private Declare Function CharToOem Lib "user32" Alias "CharToOemA" (ByVal lpszSrc As String, ByVal lpszDst As String) As Long
        '''Translates a string into the OEM-defined character set.
    Private Declare Function MapVirtualKey Lib "user32" Alias "MapVirtualKeyA" (ByVal uCode As Long, ByVal uMapType As enumMAPVK) As Long
        '''Translates (maps) a virtual-key code into a scan code or character value, or translates a scan code into a virtual-key code.
    Private Declare Sub Keybd_event Lib "user32" Alias "keybd_event" (ByVal bVk As Byte, ByVal bScan As Byte, ByVal dwFlagsv As enumKBE, ByVal dwExtraInfo As Long)
        '''Synthesizes a keystroke. The system can use such a synthesized keystroke to generate a WM_KEYUP or WM_KEYDOWN message.
    Private Declare Function OemKeyScan Lib "user32" (ByVal wOemChar As Long) As Long
        '''Provides information that allows a program to send OEM text to another program by simulating keyboard input.
    Private Declare Function VkKeyScan Lib "user32" Alias "VkKeyScanA" (ByVal cChar As Byte) As Integer
        '''Translates a character to the corresponding virtual-key code and shift state for the current keyboard.:
#End If


Sub CopyWithoutEnter§()    
'''Upon copying 1 cell, it will take only the cell-value into the clipboard,
'''without CR/LF at the end and without losing undo/redo.
'''You wan't see the dashed border around 1 cell copied anymore !!!
Dim DataObj As New MSForms.DataObject
    If Selection.CountLarge = 1 Then
        Selection.Copy
        DataObj.SetText Selection.Value
        DataObj.PutInClipboard
    Else
        Selection.Copy
    End If
End Sub
Sub PasteWithoutFormat§()  
'''Paste without any formats and without losing undo/redo.
'''Handles range-objects and data from other applications on the clipboard.
'''Need to add button "Paste Values" to the Quick Access Toolbar at position 4
'''Need to add button "Paste and keep text only" to the Quick Access Toolbar at position 5
Dim DataObj As New MSForms.DataObject
    DataObj.GetFromClipboard
    If Application.CutCopyMode = 1 Then '''range-object to paste, "Paste Values"
        modKey = 165    '''=AltR
        keyNr = "4"
    ElseIf DataObj.GetFormat(1) Then    '''some text to paste, "Paste and keep text only"
        modKey = 165    '''=AltR
        keyNr = "5"
    Else                                '''something like a picture to paste, "Paste" normal
        modKey = 163    '''=CtrlR
        keyNr = "v"
    End If
    str2Char = "  "                         '''str2Char need to be 2 characters long
    CharToOem keyNr, str2Char               '''fill str2Char with the character translation
    scanCode = OemKeyScan(Asc(str2Char))    '''maps Oem-ASCII codes into the OEM-scancodes
    keyCode = VkKeyScan(Asc(keyNr))         '''translates character to virtual keycode
    startTime = Now()
    Do '''check if Ctrl is not pressed anymore
        DoEvents    '''enable to release keys
        If Now() > startTime + TimeValue("00:00:02") Then '''to long waiting on key-release
            If MsgBox("Please release all mouse and keyboard buttons." _
                , vbOKCancel, "Function PasteWithoutFormat§()") = vbCancel Then Exit Sub
            startTime = Now() '''get start time waiting
        End If
    Loop Until CBool(GetKeyState(17) And -128) = 0 or modKey = 163 
    Application.OnKey "^v" '''cancel key-capturing incase it's Ctrl+V
    Keybd_event modKey, 0, KBE_KeyDown + 1, 0       '''press AltR or CtrlR  +1=ext.key
    Keybd_event 162, 0, KBE_KeyUp, 0                '''release CtrlL
        '''NEEDED FOR BUG: When pressing AltR, the CtrlL also go's down on some PC's (like Belgium azerty AltR=AltGr)
    Keybd_event (keyCode And &HFF), (scanCode And &HFF), KBE_KeyDown, 0
    Keybd_event (keyCode And &HFF), (scanCode And &HFF), KBE_KeyUp, 0
    Keybd_event modKey, 0, KBE_KeyUp + 1, 0         '''release AltR or CtrlR  +1=ext.key
    DoEvents
    Application.OnKey "^v", "PasteWithoutFormat§"
End Sub
Now we capture Ctrl+V and Ctrl+C
Press Ctrl+R, select ThisWorkbook and add this code:
Private Sub Workbook_Open()
    Application.OnKey "^c", "CopyWithoutEnter§"
    Application.OnKey "^v", "PasteWithoutFormat§"
End Sub