Changing Excel's default paste mode to values only, Grabbing text from cell stored in object

[CodePest]

Hello,

I am attempting to make it so excel will only paste values when the command CTL + V is used. So, easy enough; I wrote this code and set the short cut key to CTL + V in the macro option window.

Sub PasteValuesOnly()

' Prevents screen flashing during hide/unhide, turns off screen updating
    Application.ScreenUpdating = False
    
' Establish Worksheet Names
    Dim Tracker As Worksheet
    Set Tracker = ActiveWorkbook.Worksheets("sheet1")
    
    Dim Sales As Worksheet
    Set Sales = ActiveWorkbook.Worksheets("sheet2")
            
' Turn off protection
        Dim Pw1 As Integer
        Pw1 = 123
        Tracker.Unprotect Pw1
        Sales.Unprotect Pw1


    Dim myRange As Range
    Set myRange = Selection
        Debug.Print myRange.Address
    
    Dim DataObj As MSForms.DataObject
    Set DataObj = New MSForms.DataObject
    
    On Error GoTo EmptyClip
    
    '~~> Get data from the clipboard.
        DataObj.GetFromClipboard
    
    '~~> Get clipboard contents
        myString = DataObj.GetText(1)
            Debug.Print myString
    
    ' Copy string to selection
        myRange = myString
        myRange.Locked = False
    
' Prevents screen flashing during hide/unhide, turns off screen updating
    Application.ScreenUpdating = False
    
    Sales.Protect Pw1
    Tracker.Protect Pw1
    
    Exit Sub
    
EmptyClip:
    If Err <> 0 Then MsgBox "Value not allowed."
    Sales.Protect Pw1
    Tracker.Protect Pw1
    
End Sub

It works, I can copy text from the web and paste it into a cell and my formatting is not affected. Here is the problem

THE PROBLEM:

When I copy a cell and paste I get an error, unless I copy from the formula bar (which defeats the purpose of the code). For some reason, my code is not grabbing the text from the clipboard when copying a cell?

ERROR:
Run-time error '-2147221404(80040064)':

DataObject:GetText Invalid FORMATETC structure

Any help would be appreciated.

 

[CodePest]

Ok, it appears the issue is related to the sheet being protected. However, I would assume that using Sheet.Unprotect would rectify the issue. Not sure why it works on an unprotected sheet, but not on a protected sheet.

 

[CodePest]

SOLUTION:

When the command Unprotect is used, excel will clear the clipboard. The simple solution was moving the unprotect line of my script to just after the clipboard text is stored in a string. When the sheet is unprotected, the clipboard is cleared but the text will remain in the string.

Here is the correct code for changing Excel's default paste mode to values only. Lastly, adding short cut key under macro options to CTL + v will make it so anything users copy and paste into your spreadsheet will not interrupt existing formats.

[FONT=Verdana]Sub PasteValuesOnly()[/FONT]

' Prevents screen flashing during hide/unhide, turns off screen updating
    Application.ScreenUpdating = False
    
' Establish Worksheet Names

    On Error GoTo OutofRange
    Dim Tracker As Worksheet
    Set Tracker = ActiveWorkbook.Worksheets("sheet1")
    
    Dim Sales As Worksheet
    Set Sales = ActiveWorkbook.Worksheets("sheet2")

    Dim myRange As Range
    Set myRange = Selection
        Debug.Print myRange.Address
    
    Dim DataObj As MSForms.DataObject
    Set DataObj = New MSForms.DataObject
    
    On Error GoTo EmptyClip
    
    '~~> Get data from the clipboard.
        DataObj.GetFromClipboard
    
    '~~> Get clipboard contents
        myString = DataObj.GetText(1)
            Debug.Print myString

' Turn off protection
        Dim Pw1 As Integer
        Pw1 = 123
        Tracker.Unprotect Pw1
        Sales.Unprotect Pw1

    ' Copy string to selection
        myRange = myString
        myRange.Locked = False
    
' Prevents screen flashing during hide/unhide, turns off screen updating
    Application.ScreenUpdating = False
    
    Sales.Protect Pw1
    Tracker.Protect Pw1
    
    Exit Sub
    
EmptyClip:
    If Err <> 0 Then MsgBox "Value not allowed."
    Sales.Protect Pw1
    Tracker.Protect Pw1
    Exit Sub

OutofRange:
    If Err <> 0 Then MsgBox "Value out of range. Please close " & ThisWorkbook.Name & " to resume normal Cut/Copy/Paste mode."
     
[FONT=Verdana]End Sub[/FONT]