Paste to Textbox Code

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,039
Office Version
  1. 2016
Platform
  1. Windows
Hi

Could someone please help me in improving my code below. I use it to paste URLs from a TextBox ("TextBoxPaste") to a sheet and then the results show in the listbox.
The code allows me to paste multiple rows on URls into the textbox which then are placed in Sheet7 A2 down 1 url per row. THIS BIT IS FINE

My Problems are as such.
See image attached, Textbox is shown in yellow.
  1. Even if the textbox is NOT selected and the user click on the button "Paste from Clipboard", the URLs are pasted in.
  2. Even if there is NO copied URLs in the clipboard the following message pops up MsgBox "Your Copied Urls Have Been Pasted Now Click Ok and then Start"

I need the code to runs as such
  1. The user clicks inside the text box, then
  2. The user click on the button "Paste from Clipboard", If NO Urls are copied then message = "There are NO urls to paste, please copy some first then try again"
  3. If there are URLs copied then it runs the code and final message MsgBox "Your Copied Urls Have Been Pasted Now Click Ok and then Start"

VBA Code:
Private Sub CommandButton1_Click()
'Pastes from ClipBoard
Dim objdataobject As MSForms.DataObject
Set objdataobject = New MSForms.DataObject
Dim Str As String, a
Dim cnt As Integer
Dim w()
 
objdataobject.GetFromClipboard
  If Me.TextBoxPaste.Value = "" Then
   On Error Resume Next
    Me.TextBoxPaste.Value = objdataobject.GetText
     Str = TextBoxPaste.Value
      a = Chr(10)
  cnt = UBound(Split(Str, a))
 
On Error Resume Next
ReDim w(1 To cnt + 1, 1 To 1)
 For i = 0 To cnt
   w(i + 1, 1) = Split(Str, Chr(10))(i)
 Next i
Sheet7.Range("A2").Resize(i, 1) = w

TextBoxPaste.Value = ""
MsgBox "Your Copied Urls Have Been Pasted Now Click Ok and then Start"

Exit Sub
End If
End Sub
 

Attachments

  • Screenshot.jpg
    Screenshot.jpg
    36 KB · Views: 8

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,039
Office Version
  1. 2016
Platform
  1. Windows
Hi

I have added this to the the code, which pops up the mesage if there are no URLs copied, but can't get it to run the rest of the code

VBA Code:
If Len(Me.TextBoxPaste.Value) = 0 Then
            With Me.TextBoxPaste
                 MsgBox "You Must Copy some URLs to paste", vbCritical, "ERROR"
                .Value = "": .SetFocus
            End With
            Exit Sub
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Try this

VBA Code:
Option Explicit
Private isSelected As Boolean

Private Sub CommandButton1_Click()
'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 Urls Have Been Pasted Now Click Ok and then Start"

    If Not isSelected Then
        msg = "textbox not selected"
    Else
        objdataobject.GetFromClipboard
   
        If Me.TextBoxPaste.Value = "" Then
            On Error Resume Next
            Me.TextBoxPaste.Value = objdataobject.GetText
            Str = TextBoxPaste.Value
            a = Chr(10)
            cnt = UBound(Split(Str, a))
            If Len(Str) < 5 Then
                msg = "nothing copied"
            Else
                On Error Resume Next
                ReDim w(1 To cnt + 1, 1 To 1)
                For i = 0 To cnt
                    w(i + 1, 1) = Split(Str, Chr(10))(i)
                Next i
                Sheet7.Range("A2").Resize(i, 1) = w
            End If
            TextBoxPaste.Value = ""
        End If
    End If
    isSelected = False
    MsgBox msg
End Sub
Private Sub TextBoxPaste_GotFocus()
    isSelected = True
End Sub
 

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,039
Office Version
  1. 2016
Platform
  1. Windows
It keeps stating that the textbox is NOT selected, I don't think the onfocus is working
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How odd - works for me
I wonder what is different in your workbook ?

Did you place this at top of sheet code module ?
VBA Code:
Private isSelected As Boolean

Did you place this in the same module ?
VBA Code:
Private Sub TextBoxPaste_GotFocus()
    isSelected = True
End Sub
 

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,039
Office Version
  1. 2016
Platform
  1. Windows
see image
 

Attachments

  • Screenshot001.jpg
    Screenshot001.jpg
    114.6 KB · Views: 1

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

That is why it does not work !
Why did you change the name of the sub ?

BAD
Private Sub TextBoxPaste_on_Focus

GOOD
Private Sub TextBoxPaste_GotFocus()
 

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,039
Office Version
  1. 2016
Platform
  1. Windows
Not sure why GotFucus() changed.

I have changed it back but still having the problem. I should have mentioned that the userform puts data in Sheet7 BUT does not have to be open on Sheet7

I have made a small change, and will get rid of the textbox as i can't get the code to work so after changing

VBA Code:
  If Not isSelected Then
TO
VBA Code:
 If isSelected Then

I can stop it from stating that the TextBox has not been selected. If NO data is in the clipboard, then it fires up the 3rd message of "Nothing Copied"
VBA Code:
If Len(Str) < 5 Then
                msg = "nothing copied"

So if there is data it pastes it over and gives 1st message and if there is NO data to paste then give out the 3rd message "Nothing Copied"

Not sure why it is not working for me but this is still better than what I had,

Thanks for taking the time out to help
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
I cannot see the image on your first post for some reason
- is the textbox on a userform ?
- that would explain why it is not working
 

Watch MrExcel Video

Forum statistics

Threads
1,123,346
Messages
5,601,079
Members
414,426
Latest member
fraru

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
Top