Not allow paste, but allow VBA code to paste

Question

Hello all,

I've run into a situation where the users of my workbook paste a row into the workbook, and that unfortunately circumvents my data validation, and seems to prevent the VBA code from running.

Below is the code I'm using for the workbook. The goal is to have the closed items to go to a historical page. When the items are pasted (or inserted) from the user's other workbook, it seems to bypass the data validation, and the code. I don't care if the users paste from another workbook, I just don't want the data validation or code to not work.

Is there a way to prevent the users from pasting whole rows or columns, but still allow the code to run/paste items? Or a way for the code and data validation to be more robust and not care whether or not someone pastes from another workbook?

Thanks everyone. I appreciate your help. Your help has been tremendous over my time on this forum.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim ws As Worksheet: Set ws = Sheets("Historical")

If Intersect(Target, Sh.Columns("H:H")) Is Nothing Then Exit Sub
        If Target.Count > 1 Then Exit Sub
        If Target.Value = vbNullString Then Exit Sub
        If Sh.Name = "Historical" Then Exit Sub

Application.ScreenUpdating = False
Application.EnableEvents = False

If Target.Value = "Closed" Then
             Target.Offset(, 2) = Date
             Target.EntireRow.Copy ws.Range("A" & Rows.Count).End(3)(2)
             Target.EntireRow.Delete
        End If

ws.Columns.AutoFit

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Windows / Edge 128.0.0.0

vcoolio · Answer

Hello Sean

Is there a way to prevent the users from pasting whole rows or columns

The old fashioned way would be to give the offender a clip under the ear!

Data validation has always been a problem to keep safe from overwriting.

However, you could try something like this code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim tVal As String, ListVal As String, ListVal2 As String

If Target.Count > 1 Then Exit Sub

Application.EnableEvents = False

tVal = Target.Value
        On Error Resume Next
ListVal = Target.Validation.InCellDropdown
        On Error GoTo 0
Application.Undo
    On Error Resume Next
ListVal2 = Target.Validation.InCellDropdown
        On Error GoTo 0
If ListVal = ListVal2 Then
     Target = tVal
         Else
            MsgBox "DO NOT ATTEMPT TO PASTE VALUES INTO DATA VALIDATED CELLS. PLEASE USE THE DROP DOWN VALUES ONLY.", vbExclamation, "WARNING"
     End If
      
Application.EnableEvents = True
Application.CutCopyMode = False

End Sub

Please note that this works at the WorkSheet level not the WorkBook level as your other code does. Hence, this code needs to be placed in the worksheet module of the affected worksheet (I'm assuming this is what you'd like).

Test it in a copy of your workbook first and see what happens.

Cheerio,

vcoolio.

vcoolio · Answer

Hello Sean,

Hmmm.....viciouscoolio. Has a certain ring to it!

It'd probably be best to upload your sample workbook with all the codes you have in it left in their modules. I'll have a play with it before I once again disappear from the forum.

Should I remove the original code?

Nope. I'll check it out and see how it interacts with the code in post #1.

Have a great weekend!

Cheerio,

vcoolio.

Not allow paste, but allow VBA code to paste

2 responses

Your reply

Similar discussions

Subscribe To Our Newsletter!