CODEWITHSUNDEEP
excelvbavalidationcopy-paste
Josepiedehierroa
Josepiedehierroa

Reputation: 45

Get clipboard value for data validation

I have an Excel file, where in a specific range of cells, if a change is made, a change event macro is triggered.

This macro checks if the last action is any type of pasting.

I need to get, in a variable the content the user has copied (clipboard?) and then execute a function or procedure which checks the validy of the data. If it's correct, it will paste the values mantaining the conditional format, and if wrong it will undo the operation and disable the events.

I stated range(B:B) to keep it simple. In reality I will have a function for each column because the validation changes. (I will replicate the logic.)

Private Sub Worksheet_Change(ByVal Target As Range)
    lastAction = Application.CommandBars("Standard").FindControl(ID:=128).List(1)
    
    If Left(lastAction, 5) = "Paste" Then
        If Not Application.Intersect(Target, Range("B:B")) Is Nothing Then
            validation (Application.Intersect(Target, Range("B:B")))
        End If
    Else
    End If
End Sub


Function validation(cell) As Boolean
    Dim check As Boolean
    check = Application.WorksheetFunction.VLookup(cell, MDM.Range("AK2:AK86"), 1, False)
    If check = True Then
        ActiveSheets.PasteSpecial Paste:=xlPasteAllMergingConditionalFormats
        Application.CutCopyMode = False
    Else
        With Application
            .EnableEvents = False
            .Undo
        End With
        Application.EnableEvents = True
    End If
End Function

I need to do this validation because if the user pastes the value from another Excel file, it will remove both the conditional formatting and the data validation for that column.

enter image description here

Upvotes: 0

Views: 148

Answers (1)

exception
exception

Reputation: 326

I've used the clsClipboard class described at the following link

http://www.la-solutions.co.uk/content/CONNECT/MVBA/MVBA-Clipboard.htm

Copy the VBA class module code to a file named clsClipboard.cls, then import new Class module into your project.

Usage:

Sub test()
    Dim CB As New clsClipboard
    Dim myVar As String

    CB.SetText "this is a test"

    myVar = CB.GetText()

    Debug.Print myVar
    
    Set CB = Nothing
End Sub

Upvotes: 1

Related Questions