Copy and paste from clipboard to textbox

[Sharid]

My following code was working, but now I am having a few issues, When the textbox is selected it pasted the data from the clipboard. If it is NOT selected then it gives error messages advising the user to either select the textbox or there is nothing in the clipboard to paste.

In the textbox I had this code, to ensure if it was NOT selected then it would fireup a message for the user.

Private Sub PasteTextBox_Enter ()
   isSelected = True
End Sub

Changes I was making
I replaced the msgbox with small userform that look a bit more professional, problem is the code does not behave the same with the userforms as it did with the message boxes

Problems

1. When the textbox is selected, it keeps firing up the message that the textbox is NOT selected, this NOW happens if I use the message box process OR userform messages.
2. When the code is run it shows all the userform messages. Originally with the message box it would only show the relevant message.

Main Code
I have left the original message in the code, they are commented out all I have done is changed the msg box to userform.

Private Sub PasteBt_Click()
'''Paste to sheet9 from  textbox 'Pastes from ClipBoard

    Dim objdataobject As MSForms.DataObject
    Set objdataobject = New MSForms.DataObject
    Dim Str As String, a, i As Long, msg As String
    Dim cnt As Integer
    Dim w()

'standard message
    'msg = "Your Copied Data Has Been Pasted Now Click then Start button"
        PasteSucess.Show 'using userforms as messages
    
If Not isSelected Then
        'msg = "**** INFO **** You have NOT selected the Paste box **** INFO **** "
        PasteMessage.Show 'using userforms as messages
    Else
        objdataobject.GetFromClipboard
  
        If Me.PasteTextBox.Value = "" Then
            On Error Resume Next
               Me.PasteTextBox.Value = objdataobject.GetText
                  Str = Replace(PasteTextBox.Value, Chr(13), "")
               a = Chr(10)
            cnt = UBound(Split(Str, a))
            If Len(Str) < 5 Then
                'msg = "* ERROR * You need to copy some Data in order to Paste * ERROR *"
                NothingToPaste.Show 'using userforms as messages
            Else
                On Error Resume Next
                ReDim w(1 To cnt + 1, 1 To 1)
                For i = 0 To cnt
                w(i + 1, 1) = Replace(Split(Str, Chr(10))(i), Chr(13), "")
                    'w(i + 1, 1) = Split(Str, Chr(10))(i)
                Next i
                Sheet9.Range("A2").Resize(i, 1) = w
            End If
            PasteTextBox.Value = ""
        End If
    End If
    isSelected = False
    'MsgBox msg
    PasteMessage.Show 'using userforms as messages
End Sub

These are the two userforms that I am using as messages, both popup and message 2 is on top of message 2, I have placed them next to each other for your viewing.

Original thread My post on Mr Excel

 

[Sharid]

Hi

I tried to change the above code to an If statement, however it is not showing the right userform as a message, at the right time.
On userform Initilize Sheet 10 Cell "I5" is set to the value of False, when the textbox is selected it changes to True.

How it should work. see NEW CODE below

• If Sheet10 Cell I5 = Fasle then run Point 1 of the code, stating that the textbox is NOT selected
• If textbox is selected BUT clipboard is EMPTY then run point 2 of the code and should show the message.
• If Sheet10 Cell I5 = True and ClipBoard has data to paste then, run point 3 and 4 of the code, this work fine apart from Point 1 Code also runs as the value is set back to False.

Private Sub PasteBt_Click()
'''Paste to sheet9 from textbox
'''Pastes from ClipBoard

    Dim objdataobject As MSForms.DataObject
    Set objdataobject = New MSForms.DataObject
    Dim Str As String, a, i As Long, msg As String
    Dim cnt As Integer
    Dim w()

' 1 ################# IF False then show Userform Message #################    
If Sheet10.Range("I5") = False Then 'If set to false the show this userform message
    PasteMessage.Show '''using userforms as messages' Textbox is NOT Selected
End If
'#############################################################

  objdataobject.GetFromClipboard
  
 If Me.EmailPasteTextBox.Value = "" Then
            On Error Resume Next
            Me.PasteTextBox.Value = objdataobject.GetText
            Str = Replace(PasteTextBox.Value, Chr(13), "")
            a = Chr(10)
            cnt = UBound(Split(Str, a))

' 2 ############ if clipboard empty then show userform message ##################
            If Len(Str) < 5 Then
                '''message to state clipboard is empty
                NothingToPaste.Show 'using userforms as messages
'###################################################################


' 3 #################### paste the data ########################
            Else
            Sheet9.Range("A1").Value = "'Pasted Data"   '''TITLE Sheet9 A1
                On Error Resume Next
                ReDim w(1 To cnt + 1, 1 To 1)
                For i = 0 To cnt
                w(i + 1, 1) = Replace(Split(Str, Chr(10))(i), Chr(13), "")
                  
                Next i
                Sheet9.Range("A2").Resize(i, 1) = w
            End If
'####################################################################

' 4 ################## sucessful paste message ##################################

            PasteSucess.Show '''Message to show sucessfull paste
            PasteTextBox.Value = "" '''set textbox to nothing
            Sheet10.Range("I5").Value = "False" '''set the valye back to FALSE from True
        End If
'####################################################################
End Sub

 

[Sharid]

I have fix the problem with this code. I am however having an issue with selecting the textbox. See below, for more info.

Private Sub PasteBt_Click()
'''Paste to sheet9 from email tab textbox
'''Pastes from ClipBoard

    Dim objdataobject As MSForms.DataObject
    Set objdataobject = New MSForms.DataObject
    Dim Str As String, a, i As Long, msg As String
    Dim cnt As Integer
    Dim w()
   
    If Sheet10.Range("I5").Value = "False" Then
      
       PasteMessage.Show 'using userforms as messages
    Else
        objdataobject.GetFromClipboard

        If Me.PasteTextBox.Value = "" Then
            On Error Resume Next
            Me.PasteTextBox.Value = objdataobject.GetText
          
            Str = Replace(PasteTextBox.Value, Chr(13), "")
            a = Chr(10)
            cnt = UBound(Split(Str, a))
            If Len(Str) < 5 Then
               NothingToPaste.Show 'using userforms as messages
            Else
            Sheet9.Range("A1").Value = "Pasted" 'TITLE Sheet9 A1
                On Error Resume Next
                ReDim w(1 To cnt + 1, 1 To 1)
                For i = 0 To cnt
                w(i + 1, 1) = Replace(Split(Str, Chr(10))(i), Chr(13), "")
                Next i
                Sheet9.Range("A2").Resize(i, 1) = w
                PasteTextBox.Value = ""
                PasteSucess.Show 'using userforms as messages
          End If
          End If
          End If

   If Sheet10.Range("I5").Value = "True" Then
            Sheet10.Range("I5").Value = "False"
   End If
End Sub

Selecting textbox Isuues
When the textbox is selected it does NOT always change the value to "True". I have tried it with repaint and without. I sometime have to click out of the textbox and the reselect the textbox. Can anyone suggest a work around.

Private Sub PasteTextBox_Enter()
Sheet10.Range("I5").Value = "True"
'UserForm1.Repaint
End Sub

 

[Sharid]

Its ok, I think I have done it I think.

After my main code runs, in the last IF statement. I added this

ListBox1.SetFocus

The focus is now taken off the textbox, so when the I select it again it seems to work fine.