Paste to Textbox Code

[Sharid]

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"

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

 

[Sharid]

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

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]

Try this

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]

It keeps stating that the textbox is NOT selected, I don't think the onfocus is working

 

[Yongle]

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

Did you place this at top of sheet code module ?

Private isSelected As Boolean

Did you place this in the same module ?

Private Sub TextBoxPaste_GotFocus()
    isSelected = True
End Sub

 

[Sharid]

see image

 

[Yongle]

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]

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

  If Not isSelected Then

TO

 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"

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]

Not sure why GotFucus() changed

that is also incorrect

 

[Yongle]

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