MsgBox & Pasting from ClipBoard-CodePudding

I've got 3 bits of code - trying to make them work together. On double click, if conditions met -- paste happens. If target cell is not empty, then message box with yes/no pops up. If user wants to overwrite the cell, code should do it. But, getting error in Sub Paste() on:

Target.Value = S

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 If Not Application.Intersect(Target, Range("k4:s8")) Is Nothing And Target.Value = "" Then
 Cancel = True
 Call Paste
 'Else: MsgBox "If appropriate, clear the cell first. This is to prevent unintended data overwrite.", vbOKOnly   vbCritical, "Heads up" 'this works if MsgBox_YN_Paste won't
 Else: Call MsgBox_YN_Paste
 End If
End Sub

Sub Paste()
'Tools -> References -> Microsoft Forms 2.0 Object Library
'or you will get a "Compile error: user-defined type not defined"
  Dim DataObj As New MSForms.DataObject
  Dim S As String
  DataObj.GetFromClipboard
  S = DataObj.GetText
  'Debug.Print S 'print code in the Intermediate box in the Macro editor
  Target.Value = S
End Sub

Sub MsgBox_YN_Paste()

 Dim AnswerYes As String
 Dim AnswerNo As String

 AnswerYes = MsgBox("Do you Wish to replace contents of this cell?", vbQuestion   vbYesNo, "Heads Up!")

 If AnswerYes = vbYes Then
   'Range("A1:A2").Copy Range("C1")
    'ActiveCell.Clear
   'or?
   'Target.Value = ""
   Call Paste
 Else
   'Range("A1:A2").Copy Range("E1")
 End If

End Sub

CodePudding user response：

I'd do it like this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    If Application.Intersect(Target, Range("k4:s8")) Is Nothing Then Exit Sub
    
    If Len(Target.Value) > 0 Then
        If MsgBox("Do you Wish to replace contents of this cell?", _
                   vbQuestion   vbYesNo, "Heads Up!") <> vbYes Then Exit Sub
    End If
    
    Target.Value = ClipBoardText()
    Cancel = True
End Sub

'Tools -> References -> Microsoft Forms 2.0 Object Library
Function ClipBoardText() As String
    With New MSForms.DataObject
        .GetFromClipboard
        ClipBoardText = .GetText
    End With
End Function

CodePudding user response：

Target is not defined. Try

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 If Not Application.Intersect(Target, Range("k4:s8")) Is Nothing And Target.Value = "" Then
 Cancel = True
 Paste(Target)
 'Else: MsgBox "If appropriate, clear the cell first. This is to prevent unintended data overwrite.", vbOKOnly   vbCritical, "Heads up" 'this works if MsgBox_YN_Paste won't
 Else: Call MsgBox_YN_Paste
 End If
End Sub


Sub Paste(ByVal Target As Range)
'Tools -> References -> Microsoft Forms 2.0 Object Library
'or you will get a "Compile error: user-defined type not defined"
  Dim DataObj As New MSForms.DataObject
  Dim S As String
  DataObj.GetFromClipboard
  S = DataObj.GetText
  'Debug.Print S 'print code in the Intermediate box in the Macro editor
  Target.Value = S
End Sub

CodePudding user response：

I did some cleanup in the code, for clarity of understanding. Note that the return of the MsgBox is an integer numeric type and, therefore, the variable involved (AnswerYes, which I changed to AnswerYesOrNo (Integer), eliminating the other, AnswerNo, which is not used). The 'Debug.Print' actually prints on the VBE Immediate Windows.

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Application.Intersect(Target, Range("k4:s8")) Is Nothing And Target.Value = "" Then
        Cancel = True
        Call PasteIt(Target)
        'Else: MsgBox "If appropriate, clear the cell first. This is to prevent unintended data overwrite.", vbOKOnly   vbCritical, "Heads up" 'this works if MsgBox_YN_Paste won't
    Else: Call MsgBox_YN_Paste(Target)
    End If
End Sub

Sub PasteIt(ByRef Target As Excel.Range)
    'Tools -> References -> Microsoft Forms 2.0 Object Library
    'or you will get a "Compile error: user-defined type not defined"
    Dim DataObj As New MSForms.DataObject
    Dim S As String
    DataObj.GetFromClipboard
    S = DataObj.GetText
    'Debug.Print S 'print code in the VBE Immediate Window
    Target.Value = S
End Sub

Sub MsgBox_YN_Paste(ByRef Target As Excel.Range)

    Dim AnswerYesOrNo As Integer

    AnswerYesOrNo = MsgBox("Do you Wish to replace contents of this cell?", vbQuestion   vbYesNo, "Heads Up!")

    If AnswerYesOrNo = vbYes Then
        Call PasteIt(Target)
    End If

End Sub