Copy text from text box - TextBox name has a space in it.

[dkjonesau]

Hi all,

I have a spreadsheet that arrives each month I have to work on in VBA. Unfortunately it arrives with information in a couple of text boxes. I've tried a few different ways to copy the text to the clipboard but the text box name has a space in it and none of the solutions I've searched will accept it. Any ideas? Just want to copy the contents of "TextBox 2" to Clipboard so I can paste it into a cell (where it arguably should be in the first place but I have no control over that )

Thanks,

Dave

 

[johnnyL]

See if this code assists you at all. It is code that I found and altered a wee bit. I have not tested it, I leave that up to you to report the results:

Sub Test()
'
'   This UNTESTED code is supposed to display the name of each Text box to column B & display the contents of it to the C column.
'       Adjust columns accordingly below \/ \/ \/.
'
    Dim TextBoxCounter      As Long
    Dim item                As Shape
    Dim TextBoxNameColumn   As String
    Dim TextBoxValueColumn  As String
    Dim wsSource            As Worksheet
'
    Set wsSource = Sheets("Sheet1")                                                     ' <--- Set this to the correct sheet name
    TextBoxNameColumn = "B"                                                             ' <--- Set this to the column to display Text box names
    TextBoxValueColumn = "C"                                                            ' <--- Set this to the column to display Text box values
'
    TextBoxCounter = 0
'
    For Each item In wsSource.Shapes
        If item.Type = msoTextBox Then
            TextBoxCounter = TextBoxCounter + 1
            Range(TextBoxNameColumn & TextBoxCounter).Value = wsSource.TextBoxes(TextBoxCounter).Name   ' Display name of Text box
            Range(TextBoxValueColumn & TextBoxCounter).Value = item.TextFrame.Characters.Text           ' Display value of Text box
        End If
    Next
End Sub

 

[Norie]

Dave

What have you tried?

 

[dkjonesau]

Dave

What have you tried?

A variety of versions of similar to this from other posts/forums.
Private Sub CommandButton3_Click()
With New MSForms.DataObject
.SetText TextBox2.Text
.PutInClipboard
End With
End Sub

The code @johnnyL posted above has worked producing the below

I can't guarantee that the TextBox number will remain consistent. I need the text out of what is currently TextBox 3 in the clipboard.

I probably have two options. I'm trying to work out now how to select the contents of a cell if it contains *published* & only copy that cell. I could autofilter it but that seems a bit over the top. Lots of posts on copying rows if one cell contains text, but not just that cell.

Alternatively, and probably more elegantly if I could modify @johnnyL 's solution to copy the text from the text box that contains *published* that would be awesome.

That way if the text box number changes the Sub is bullet proof. The target text box will always contain the word "published"

Cheers

Dave

 

[johnnyL]

@dkjonesau can you post some examples of what results you are getting and what you would like the results to look like?

 

[johnnyL]

In the Mean Time you might try this:

Sub TestV2()
'
'   This UNTESTED code is supposed to display the name of a Text box which contents contains the text of "*PUBLISHED*" to
'   the AL column & display the contents of that Text box to the AM column.
'       Adjust columns accordingly below \/ \/ \/.
'
    Dim TextBoxCounter      As Long
    Dim item                As Shape
    Dim TextBoxNameColumn   As String
    Dim TextBoxValueColumn  As String
    Dim wsSource            As Worksheet
'
    Set wsSource = Sheets("Sheet1")                                                     ' <--- Set this to the correct sheet name
    TextBoxNameColumn = "AL"                                                             ' <--- Set this to the column to display Text box names
    TextBoxValueColumn = "AM"                                                            ' <--- Set this to the column to display Text box values
'
    TextBoxCounter = 0
'
    For Each item In wsSource.Shapes
        If item.Type = msoTextBox Then
            TextBoxCounter = TextBoxCounter + 1
'
            If InStr(1, (item.TextFrame.Characters.Text), "PUBLISHED") > 0 Then
                Range(TextBoxNameColumn & TextBoxCounter).Value = wsSource.TextBoxes(TextBoxCounter).Name   ' Display name of Text box
                Range(TextBoxValueColumn & TextBoxCounter).Value = item.TextFrame.Characters.Text           ' Display value of Text box
            End If
        End If
    Next
End Sub

 

[dkjonesau]

Hi @johnnyL

I've created a test file so I don't have to redact much.

Using this as an example

And your code modified to the below

Sub johnnyL_Test()
'
'   This UNTESTED code is supposed to display the name of each Text box to column B & display the contents of it to the C column.
'       Adjust columns accordingly below \/ \/ \/.
'
    Dim TextBoxCounter      As Long
    Dim item                As Shape
    Dim TextBoxNameColumn   As String
    Dim TextBoxValueColumn  As String
    Dim wsSource            As Worksheet
'
    Set wsSource = Sheets(1)                                                     ' <--- Set this to the correct sheet name
    TextBoxNameColumn = "I"                                                             ' <--- Set this to the column to display Text box names
    TextBoxValueColumn = "J"                                                            ' <--- Set this to the column to display Text box values
'
    TextBoxCounter = 0
'
    For Each item In wsSource.Shapes
        If item.Type = msoTextBox Then
            TextBoxCounter = TextBoxCounter + 1
            Range(TextBoxNameColumn & TextBoxCounter).Value = wsSource.TextBoxes(TextBoxCounter).name   ' Display name of Text box
            Range(TextBoxValueColumn & TextBoxCounter).Value = item.TextFrame.Characters.Text           ' Display value of Text box
        End If
    Next
End Sub

Your code returns the text box text beautifully.

What I really need is just the text string with the word Published in it. Either in clipboard or I guess just in a string variable. I've been looking for ways to text search a range and pick a cell from it's contents to clipboard. I only want that cell. Most examples I've found want to copy the whole row or column and seem overly complicated for a simple task.

If there's a way of adding to your routine so that rather than putting the text box contents into a table it finds the content of the required box and either puts it in the clipboard so I can paste it, or just sets it in string variable so that I can use it, that would be fine.

Thanks,

Dave

 

[johnnyL]

That is the older code, can you please give opinion on Post #6?

 

[johnnyL]

All results of current script don't have to be saved to a sheet.

Another question, how many of these text box captures will be required? Is there only 1 that you are looking for, or could there be multiple? In other words, once 'PUBLISHED' is found, are we done looking?

Your answer will determine how results can be saved.

 

[dkjonesau]

Ended up sorting it out. Modified @johhnyL 's solution and just had what I needed placed into a String variable. Then elsewhere in the schema I put that into the cell I needed to put it. I'll go through & tidy up the comments now. At the moment some of the redundant lines are just commented out.

Cheers. Thanks @johnnyL for the help

DJ

Sub PublishedDateExtract()
'
'   This UNTESTED code is supposed to display the name of a Text box which contents contains the text of "*PUBLISHED*" to
'   the AL column & display the contents of that Text box to the AM column.
'       Adjust columns accordingly below \/ \/ \/.
'
    Dim TextBoxCounter      As Long
    Dim item                As Shape
'    Dim TextBoxNameColumn   As String
    Dim TextBoxValueColumn  As String
    Dim wsSource            As Worksheet
'
    Set wsSource = Sheets(1)                                                     ' <--- Set this to the correct sheet name
'    TextBoxNameColumn = "AL"                                                             ' <--- Set this to the column to display Text box names
    TextBoxValueColumn = "AM"                                                            ' <--- Set this to the column to display Text box values
'
    TextBoxCounter = 0
'
    For Each item In wsSource.Shapes
        If item.Type = msoTextBox Then
            TextBoxCounter = TextBoxCounter + 1
'
            If InStr(1, (item.TextFrame.Characters.Text), "PUBLISHED") > 0 Then
'                Range(TextBoxNameColumn & TextBoxCounter).Value = wsSource.TextBoxes(TextBoxCounter).name   ' Display name of Text box
                PublishedDate = Chr(34) & item.TextFrame.Characters.Text & Chr(34)          ' Display value of Text box
'    MsgBox PublishedDate
            End If
        End If
    Next

End Sub