Excel - make ctrl-v paste without formatting if the clipboard contains text or a range

Question

I have am Excel document with reasonably complex conditional formatting. The user group is not conversant with spreadsheets, mostly using them as a way to make text line up in tables, so I can't rely on training to Paste Special Values; if ctrl-v doesn't do it, it's not happening. Likewise modifying my instance of Excel with Quick Access or similar.

I've gotten as far as a simple VBA Macro to which I can remap ctrl-v. There are two single line commands that work - but each for only a given type of content. This is the macro:

Sub paste_value()
'
' paste_value Macro
' paste value only, preserving format
'
' Keyboard Shortcut: Ctrl+v
'
    ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
        False
     
     Selection.PasteSpecial Paste:=xlPasteValues
End Sub

ActiveSheet.PasteSpecial works if the clipboard holds text, most often from another program, but Selection.PasteSpecial throws run-time error 1004.

Selection.PasteSpecial works if the clipboard holds an Excel range, but ActiveSheet.PasteSpecial throws run-time error 1004.

A test to see what's on the clipboard and choose the appropriate command could work, but I've no idea how to go about that test. Some quick googling makes it seem a daunting task that might interfere with the clipboard itself.

Also open to any other methods that work.

Answer 1

Try this code:

Sub paste_value()
'
' paste_value Macro
' paste value only, preserving format
'
' Keyboard Shortcut: Ctrl+v
'
    On Error Resume Next
    ActiveSheet.PasteSpecial Format:="HTML", Link:=False, _
        DisplayAsIcon:=False, NoHTMLFormatting:=True
    If Err = 0 Then Exit Sub
     
    Selection.PasteSpecial Paste:=xlPasteValues
End Sub

Answer 2

MGonet's answer is good. Using the error state to break out is pretty cool; I made one modification to handle plain text as well:

Sub paste_value()
'
' paste_value Macro
' paste value only, preserving format
'
' Keyboard Shortcut: Ctrl+v
'
    On Error Resume Next
    
    ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
        False
    If Err = 0 Then Exit Sub
    
    ActiveSheet.PasteSpecial Format:="HTML", Link:=False, _
        DisplayAsIcon:=False, NoHTMLFormatting:=True
    If Err = 0 Then Exit Sub
     
    Selection.PasteSpecial Paste:=xlPasteValues
 
End Sub