Excel VBA Clear Clipboard [With Easy Steps]

In this article, we have written a VBA code to clear the clipboard after all copying and pasting data to ensure the safety of the dataset. Excel VBA clear clipboard ensures your data safety from theft or reverse engineering.

In Excel VBA, the clipboard is used for copying and pasting data. Clearing the clipboard means erasing the data you copied, which helps keep sensitive information safe and prevents accidental sharing. It also helps make the program run better and gives users a smoother experience. In this article, we’ll look at why clearing the clipboard is important, how to do it, and some tips for handling clipboard operations effectively.

Download Practice Workbook

Excel VBA Clear Clipboard: With 3 Easy Steps

Here we have provided a data set with clipboards.

Step 1: Insert a Module

• To add VBA code, select Visual Basic from Developer

• Now we select Insert >> Module to add VBA code.

Step 2: Copy the Code in the Module

• Now, you can write the code below.

• You can copy the code from here.

#If VBA7 Then
Private Declare PtrSafe Function AccessibleChildren Lib "oleacc" (ByVal paccContainer As Office.IAccessible, _
ByVal iChildStart As Long, ByVal cChildren As Long, _
ByRef rgvarChildren As Any, ByRef pcObtained As Long) As Long
Public Const myVBA7 As Long = 1
#Else
Private Declare Function AccessibleChildren Lib "oleacc" (ByVal paccContainer As Office.IAccessible, _
ByVal iChildStart As Long, ByVal cChildren As Long, _
ByRef rgvarChildren As Any, ByRef pcObtained As Long) As Long
Public Const myVBA7 As Long = 0
#End If
Public Sub EvRClearOfficeClipBoard()
Dim cmnB, IsVis As Boolean, j As Long, Arr As Variant
Arr = Array(4, 7, 2, 0) '4 and 2 for 32 bit, 7 and 0 for 64 bit
Set cmnB = Application.CommandBars("Office Clipboard")
With Application
.DisplayClipboardWindow = True
End With
IsVis = cmnB.Visible
If Not IsVis Then
cmnB.Visible = True
DoEvents
End If
For j = 1 To Arr(0 + myVBA7)
AccessibleChildren cmnB, Choose(j, 0, 3, 0, 3, 0, 3, 1), 1, cmnB, 1
Next
cmnB.accDoDefaultAction CLng(Arr(2 + myVBA7))
Application.CommandBars("Office Clipboard").Visible = IsVis
With Application
.DisplayClipboardWindow = True
End With
End Sub

#If VBA7 Then
Private Declare PtrSafe Function AccessibleChildren Lib "oleacc" (ByVal paccContainer As Office.IAccessible, _
ByVal iChildStart As Long, ByVal cChildren As Long, _
ByRef rgvarChildren As Any, ByRef pcObtained As Long) As Long
Public Const myVBA7 As Long = 1
#Else
Private Declare Function AccessibleChildren Lib "oleacc" (ByVal paccContainer As Office.IAccessible, _
ByVal iChildStart As Long, ByVal cChildren As Long, _
ByRef rgvarChildren As Any, ByRef pcObtained As Long) As Long
Public Const myVBA7 As Long = 0
#End If

Public Sub EvRClearOfficeClipBoard()
Dim cmnB, IsVis As Boolean, j As Long, Arr As Variant
Arr = Array(4, 7, 2, 0) '4 and 2 for 32 bit, 7 and 0 for 64 bit
Set cmnB = Application.CommandBars("Office Clipboard")
With Application
.DisplayClipboardWindow = True
End With
IsVis = cmnB.Visible
If Not IsVis Then
cmnB.Visible = True
DoEvents
End If
For j = 1 To Arr(0 + myVBA7)
AccessibleChildren cmnB, Choose(j, 0, 3, 0, 3, 0, 3, 1), 1, cmnB, 1
Next
cmnB.accDoDefaultAction CLng(Arr(2 + myVBA7))
Application.CommandBars("Office Clipboard").Visible = IsVis
With Application
.DisplayClipboardWindow = True
End With
End Sub 

Step 3: Run the Code

• Finally, run the code with the F5 key or by pressing the Run Immediately, we get the output cleared from all clipboards.

How to Disable Copy-Paste Without Clearing Clipboard in Excel

• Here we intend to create a copy of the dataset.

• We have to write the code below to disable the paste mode.

• You can copy the code from here.

Option Explicit
Public Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As LongPtr
Public Declare PtrSafe Function EmptyClipboard Lib "user32" () As LongPtr
Public Declare PtrSafe Function CloseClipboard Lib "user32" () As LongPtr
Public Function ClearClipboard()
    OpenClipboard (0&)
    EmptyClipboard
    CloseClipboard
End Function
Sub ccc()
    Call ClearClipboard
End Sub

Option Explicit
Public Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As LongPtr
Public Declare PtrSafe Function EmptyClipboard Lib "user32" () As LongPtr
Public Declare PtrSafe Function CloseClipboard Lib "user32" () As LongPtr

Public Function ClearClipboard()
    OpenClipboard (0&)
    EmptyClipboard
   CloseClipboard
End Function

Sub ccc()
    Call ClearClipboard
End Sub

• Alternatively, you can use the code below.

• You can copy the code from here.

Sub clear()
Application.CutCopyMode = False
End Sub

This line sets the Application.CutCopyMode property to False, effectively clearing any active clipboard operations and emptying the clipboard. This means that the clipboard will no longer contain any copied or cut data after this line of code is executed.

• Now, copy and paste will be off.

Things To Remember

• Be cautious when using clipboard operations within loops. If not handled properly, it could lead to infinite loops or unnecessary clipboard clearing, impacting performance and causing unintended results.
• Back up your important data before clearing the clipboard. Once you clear the clipboard, you won’t get the lost data back.

Frequently Asked Questions

1. What happens if another application is using the clipboard while I try to clear it?

If another application is using the clipboard, there might be an error during the clearing process. Proper error handling is recommended to handle such situations.

2. Can I clear the clipboard within a loop?

Yes, you can clear the clipboard within a loop, but it’s crucial to ensure that you have cleared the clipboard only when you needed to avoid unnecessary operations.

3. Are there any alternatives to clearing the clipboard in VBA?

Yes, instead of clearing the clipboard, you can set the copied data to a variable or directly paste it into another location within your VBA code.

4. Does clearing the clipboard affect the Undo feature in Excel?

Answer: Yes, clearing the clipboard will remove the copied data from the Undo stack, meaning that users won’t be able to undo the copy action.

Conclusion

In conclusion, clearing the clipboards keeps your data safe and frees up the computer. Clearing the clipboard in Excel VBA is important for keeping data safe, improving how the program runs, and making it more user-friendly. When you copy data, it goes to the clipboard, and clearing it means erasing what you copied, so others can’t accidentally see it. Excel VBA clear clipboard will help you clear all kinds of modifications you will make in data instantly. If you have any queries or suggestions, please let us know in the comments section.