Macro to delete and replace a text box

[Guest]

I use a macro in a master file to populate sub-files with data. I would like
to delete an existing text box in the sub-files and replace it with a text
box from the master file. Is there a way to do this using a macro? Any
information is appreciated.

Thanks

 

[Tom Ogilvy]

What kind of textbox - from the drawing toolbar or the control toolbox
toolbar.

Why delete and add - why not just copy the text from one to the other?

 

[Guest]

If you want to know how that works for Controls on Sheets,
then try the excel macro recorder

Greeting Jonjo

 

[Guest]

It is a text box from the drawing toolbar. In the past, I tried to set-up
the macro to go in and delete the text but it did not work. I will try it
again.

Thanks

 

[Guest]

Hi Tom

Sorry about the second reply, I am scattered all over the map. I need to
replace the single box with two new boxes. The box is from the drawing
toolbar.

Thanks
Dave

 

[Guest]

If you want to figure out how staff works try the macrorecorder
and do the things you want to do with the Textboxes.
here an example :

sub macro1()
Application.CommandBars("Picture").Visible = True
Application.CommandBars("Drawing").Visible = True
ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 44.25,
173.25, _
171#, 132.75).Select
Selection.Characters.Text = "Hallo Leute"
With Selection.Characters(Start:=1, Length:=11).Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("E22").Select
End Sub

The interesting part is The Shapes Collection of the WorkSheet that contains
several interesting things like Buttons, Graphics and so on.

Greetings Jonjo

 

[Tom Ogilvy]

Sub AAAC()
Dim wkbk1 As Workbook
Dim wkbk2 As Workbook
Set wkbk1 = Workbooks("Archive.xls")
Set wkbk2 = Workbooks("Book1")
wkbk2.Worksheets("Sheet1").TextBoxes.Delete
wkbk1.Worksheets("Sheet1").TextBoxes(1).Copy
Application.Goto wkbk2.Worksheets("Sheet1") _
.Range("B9")
wkbk2.Worksheets("Sheet1").Paste

End Sub