How to prevent users from copying and pasting data into text boxes on a user-form in Excel using macro or VBA code. Watch the video below:

First we open the Visual Basic for Applications editor by clicking on the Developer tab and then clicking on Visual Basic. We can also access the VBA editor by pressing Alt+F11 keys. In the VBA editor we click on insert and select UserForm. From the tool box we select the label control and draw a label object on the user-form. We edit the label’s caption and use a larger font. Below the label we insert a text-box:

We right-click the mouse button after selecting the text-box and click on ‘view code’. We get the following code:

Private Sub TextBox1_Change()

End Sub

We ignore this code and in the object area we select TextBox1 and in the procedure are we select ‘keydown’ to get the following code:

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

End Sub

Between these two lines of code we write our macro code to prevent pasting of data into the text-box:

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 86 And Shift = 2 Then
KeyCode = 0
End If
End Sub

The keycode 86 represents the alphabet ‘V’ and the integer (2) for Shift represents ‘Ctrl’. We know that the key combination ‘Ctrl + V’ allows us to paste copied data. We finally set the keycode to 0 which prevents an object like a text box from receiving a keystroke and thus not allowing pasting of data in the text box.

Why would anyone want to prevent copy/paste into a text box? The reason is security. You would have observed such conditions when filling out credit card numbers on banks websites.

A bit of background information on the following line of code which fires the keydown event:

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

TextBox1 is the object and it is a required object.

KeyCode: This is a required parameter. It is an integer that represents the key code of the key that was pressed or released.

Shift: a required parameter. It represents the state of SHIFT, CTRL, and ALT keys.

The settings for Shift are as follows:

Keycode constants

Further Reading:

KeyDown, KeyUp events

Keycode constants