Get clipboard value for data validation-CodePudding

I have an Excel file that are used by end users, where, in a specific range of cells, if a change is made, a change event macro is triggered.

What I do with this macro is to check if the last action is any type of pasting. What I need is to, somehow, 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.

So far, I think I am close to have everything but I am missing the knowledge to get in VBA the clipboard content in a variable.

I would appreciate general feedback as well.

PD: I have stated range(B:B) to keep it simple, in reality I will have a function for each column because the validation changes based (but that's on me,I just need to have 1 correct in order to 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 paste the value from another excel, it will remove both the conditional formatting and the data validation for that column.

CodePudding user response：

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