Macro to delete and replace a text box

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

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?

--
Regards,
Tom Ogilvy

"Dave" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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

Greeting Jonjo

"Dave" wrote:

> 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
>

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

"Tom Ogilvy" wrote:

> 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?
>
> --
> Regards,
> Tom Ogilvy
>
> "Dave" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>
>

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

"Tom Ogilvy" wrote:

> 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?
>
> --
> Regards,
> Tom Ogilvy
>
> "Dave" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>
>

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

"Dave" wrote:

> 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
>
> "Tom Ogilvy" wrote:
>
> > 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?
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Dave" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> >
> >
> >

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

--
Regards,
Tom Ogilvy

"Dave" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "Tom Ogilvy" wrote:
>
> > 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?
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Dave" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> >
> >
> >