Excel credential loop - Paste value/destination formatting only

Question

My users are getting stuck in a credential verification loop if they mistakenly paste items that have HTML in it to an Excel spreadsheet. They will sometimes be pasting from outlook or other sources and other times they will be pasting from within the workbook. I want to create a hotkey micro that will only paste the value or text on the clipboard. This is the code recorded when pasting from each scenario.

'pasting excel cell
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'pasting from Outlook/Word
ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
    False, NoHTMLFormatting:=True

It's clear from what I researched that the Selection and ActiveSheet objects handle PasteSpecial differently. The only thing I'm unsure about is how to check which object needs to be referenced for pasting the text into the selected cell so this can be as unobtrusive as possible for my users.

Scott Holtzman Scott Holtzman · Accepted Answer · 2020-04-21T21:05:52

This isn't an answer, but some code to get you started by trapping the Paste event on the worksheet in Excel. From there you can perhaps merge it with getting data directly from clipboard, or perhaps the Undo.List text will be different when pasting from Excel vs Outlook/Word and you can handle accordingly.

Private Sub Worksheet_Change(ByVal Target As Range)

    'unlock cells that get locked on paste operation
    Dim undoAction As String
    On Error Resume Next 'for when there is nothing in undo stack
    undoAction = Application.CommandBars("Standard").Controls("&Undo").List(1)
    On Error GoTo 0

    'this was specific to my needs, but you can play with it to help with your needs
    If Left(undoAction, 5) = "Paste" Then Target.Locked = False

End Sub

This info on how to Get Text From the Clipboard may also be useful. Duplicating code below in case link breaks in the future.

Function FnGetTextFromClipBoard() 

    Dim objData As New MSForms.DataObject
    Dim strText

       objData.GetFromClipboard
       strText = objData.GetText()

       'MsgBox strText
       FnGetTextFromClipBoard = strText

End Function

Excel credential loop - Paste value/destination formatting only

1 Answers