Copy and paste from clipboard to textbox

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,066
Office Version
  1. 2016
Platform
  1. Windows
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.
VBA Code:
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.

VBA Code:
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.
1593609037923.png


Original thread My post on Mr Excel
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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.

VBA Code:
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
 
Upvote 0
I have fix the problem with this code. I am however having an issue with selecting the textbox. See below, for more info.
VBA Code:
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.

VBA Code:
Private Sub PasteTextBox_Enter()
Sheet10.Range("I5").Value = "True"
'UserForm1.Repaint
End Sub
 
Upvote 0
Its ok, I think I have done it I think.

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

VBA Code:
ListBox1.SetFocus

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

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top