EXCEL VBA limit copy and paste to current column for data validation columns

Question

I have a large spreadsheet and have validation on several columns that have drop down lists.

I have the below VBA code in place that restricts user from hitting the delete button and blanking out cell in column with drop down. This works nicely but it does not prevent user from copying a cell from another column and pasting over the dropdown. Below is the code for one column.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C5:C5004")) Is Nothing Then
        If Len(Target.Text) = 0 Then
            MsgBox "You must select an item from the list!"
            Target.Select
            Application.Undo
        End If
    End If

Please advise if there is a way to limit copy and paste to the same column.

The spreadsheet that I am working with is for users to compile a large list of data, and I want to maintain data integrity with the drop down lists and validation of lengths etc. Once they are done I will take and using SSIS load the application with data in various tables as a speed load.

This is the only missing ingredient that I am needing. I am not a master at VBA which is why I am asking you.

score 0 · Answer 1 · edited May 23 '17 at 12:15

From the code present in Excel VBA How to detect if something was pasted in a Worksheet you can adapt it to have something similar to this:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lastAction As String

    ' Get the last action performed by user
    lastAction = Application.CommandBars("Standard").Controls("&Undo").List(1)

    If Not Intersect(Target, Range("C5:C5004")) Is Nothing Then
        ' Check if the cell was cleared or the last action was a paste
        If Len(Target.Text) = 0 Or Left(lastAction, 5) = "Paste" Then
            MsgBox "You must select an item from the list!"
            Target.Select
            Application.Undo
        End If
    End If

End Sub

Tip: You can also detect other actions performed in the sheet. To do so just print lastAction to a MsgBox or Debug.Print and catch the ones you need.

HTH ;)

EXCEL VBA limit copy and paste to current column for data validation columns

1 Answers1