Excel VBA changing the text within the textbox

Question

I am trying to change the number in my text box.

My code so far looks like this:

 Sub Box()
 ActiveSheet.Shapes("Asbuilt_Number1").Copy
 ActiveSheet.Range("C25").PasteSpecial
 Selection.ShapeRange.TextFrame.textRange.Characters.text = "2"
 Selection.Name = "Asbuilt_Number"
 End Sub

what is based from the Macro

 Sub Boxes_Two()
 '
 ' Macro3 Macro
 '

 '
 ActiveSheet.Shapes.Range(Array("Asbuilt_Number_1")).Select
 Selection.Copy
 ActiveSheet.Paste
 Selection.ShapeRange.IncrementLeft -0.75
 Selection.ShapeRange.IncrementTop 347.25
 Selection.ShapeRange(1).TextFrame2.textRange.Characters.text = "2"
 With Selection.ShapeRange(1).TextFrame2.textRange.Characters(1, 1). _
    ParagraphFormat
    .FirstLineIndent = 0
    .Alignment = msoAlignCenter
End With
With Selection.ShapeRange(1).TextFrame2.textRange.Characters(1, 1).Font
    .NameComplexScript = "+mn-cs"
    .NameFarEast = "+mn-ea"
    .Fill.Visible = msoTrue
    .Fill.ForeColor.RGB = RGB(0, 0, 0)
    .Fill.Transparency = 0
    .Fill.Solid
    .Size = 15
    .Name = "+mn-lt"
End With
ActiveSheet.Shapes.Range(Array("Asbuilt_Number_1")).Select
Selection.ShapeRange.Name = "Asbuilt_Number"
Selection.Name = "Asbuilt_Number"
 End Sub

I am unhappy with macro, since my copied number goes in completely different place, than I targeted.

My non-macro code throws error: Object doesn't support this property or method at the line

     Selection.ShapeRange.TextFrame.textRange.Characters.text = "2"

Even if I remove the Characters, likewise in the template below:

https://learn.microsoft.com/en-us/office/vba/api/project.shaperange.textframe2

How can I change the name of my textboxes swiftly?

Don't you work in Excel? Is your shape "Asbuilt_Number1" a sheet text box? — FaneDuru
– FaneDuru, Commented Mar 9, 2020 at 17:27
Then try this short and correct way: ActiveSheet.Range("C25").value = ActiveSheet.Shapes("Asbuilt_Number1").OLEFormat.Object.Object.text. This should replace all the code of your Sub Box(). In this way you can copy the text box value in that specific cell. What do you want to copy, in fact? Only part of the text? Can you explain that (IN WORDS)? — FaneDuru
– FaneDuru, Commented Mar 9, 2020 at 17:31
I am getting still the same error, that VBA doesn't support this method :( — Geographos
– Geographos, Commented Mar 9, 2020 at 17:35
So, do you have such a text box named "Asbuilt_Number1" on your active sheet? If yes, does it have any text load? If not, what "Asbuilt_Number1" means? — FaneDuru
– FaneDuru, Commented Mar 9, 2020 at 17:38

Dang D. Khanh · Accepted Answer · 2020-03-09 18:11:57Z

1

This works for me, let's try the following code:

Note: ActiveSheet.Range("C25").Paste will not work

Sub Box()
 With ActiveSheet
    .Shapes("Asbuilt_Number1").Copy
    [C25].Activate
    .Paste
    .Shapes(.Shapes.Count).Name = "Asbuilt_Number2"
    .Shapes("Asbuilt_Number2").TextFrame2.TextRange.Characters.Text = "2"
 End With
End Sub

answered Mar 9, 2020 at 18:11

Dang D. Khanh

1,4618 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA changing the text within the textbox

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related