Display MsgBox when clipboard is empty

Question

I'm trying to make my code that pastes the contents of the clipboard warn the user when it is empty so that the user can redo the process to avoid entering macro interrupt mode. I took reference from this post How to check if clipboard is empty of text? but I can't get mine to work, here is my code:

Sub PASTE_SYSTEM()

Application.ScreenUpdating = False
Application.Calculation = xlManual

Dim Type As String

Type = InputBox("What type of Property?", "Type?")
    If StrPtr(Type) = 0 Then Exit Sub

    ThisWorkbook.Sheets("RECEITAS_DS").Range("K2") = UCase(Type)

Dim DataObj As MSForms.DataObject
Set DataObj = New MSForms.DataObject
DataObj.GetFromClipboard
SText = DataObj.GetText(1)

If DataObj.GetFormat(1) = False Then
    MsgBox "You haven't copied anything yet or the format is incorrect!", vbCritical
    Exit Sub
End If

I tried replace if DataObj by If SText but didnt work, i still get the default excel warning

score 2 · Accepted Answer · answered May 04 '23 at 11:47

You have to change the order of your commands: First check if there is content in the clipboard - then retrieve the content:

Dim DataObj As MSForms.DataObject
Set DataObj = New MSForms.DataObject
DataObj.GetFromClipboard

If DataObj.GetFormat(1) = False Then
    'Empty clipboard
    MsgBox "You haven't copied anything yet or the format is incorrect!", vbCritical
    Exit Sub
End If

'read clipboard content to variable
SText = DataObj.GetText(1)

And another topic: don't use reserved words as variable names. Better use PropertyType than Type

Display MsgBox when clipboard is empty

1 Answers1