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]

Yes, Textbox is on the userform. I have loaded it again. When I was clicking the command button on the userform, teh focus was nolonger in the textbox I think that was the problem

 

[Yongle]

When I was clicking the command button on the userform, teh focus was nolonger in the textbox I think that was the problem

YES - that is correct

Here is amended code for use in userform - it is almost identical
A couple of the subs are different
You need to consider what else is going on in the userform that means that the textbox should be selected again by user and then add those events that are necessarry to set the boolean variable back to FALSE

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

SETS VARIABLE TO TRUE WHEN TEXTBOX IS CLICKED
Private Sub TextBoxPaste_Enter()
    isSelected = True
End Sub

FORCE COMMAND BUTTON TO TAKE FOCUS WHEN USERFORM IS LOADED
Private Sub UserForm_Initialize()
    CommandButton1.SetFocus
    isSelected = False
End Sub

 

[Sharid]

super

 

[Sharid]

When I paste the url in and they show in the list box they are showing with hidden formats,

Why is this?

and can the formats be removed, with a paste special

Please see image

 

[Yongle]

It looks like a carriage returns character at the end
- I suspect character 10 (or 13)
Does this happen on EVERY url ?

Use this formula to determine which character it is and we can take it from there
=CODE(RIGHT(A1,1))

Let me know and I will provide you with code amendment tomorrow

 

[Sharid]

Yongle

It is Character 13 and its for all urls

 

[Yongle]

Try this first:

'  Try REPLACING
    Str = TextBoxPaste.Value
 
'  WITH
    Str = Replace(TextBoxPaste.Value, Chr(13), "")

Another way:

'  TRY REPLACING THIS LINE
    w(i + 1, 1) = Split(Str, Chr(10))(i)

'  WITH
     w(i + 1, 1) = Replace(Split(Str, Chr(10))(i), Chr(13), "")

 

[Sharid]

Super that did the trick, Thanks for taking the time out to have a look

 

[Yongle]

thanks for the feedback