Macro to delete a text box and copy in new one

[Guest]

I need to set-up a macro to go from one file, file1, into another file,
file2, and delete a text box in file2. Then, I need the macro to go back
into file1 and copy a blank text box over to file2 and put it in the same
location. Is this possible? Any information is appreciated.

Thanks

 

[Dave Peterson]

You could do it with something like:

Option Explicit
Sub testme()

Dim fWks As Worksheet
Dim tWks As Worksheet
Dim fShape As Shape
Dim tShape As Shape
Dim tLocation As Range

Set fWks = Workbooks("book1.xls").Worksheets("sheet1")
Set tWks = Workbooks("book2.xls").Worksheets("sheet1")

Set fShape = fWks.Shapes("textbox1")
Set tShape = tWks.Shapes("textbox1")

Set tLocation = tShape.TopLeftCell
Application.Goto tLocation

tShape.Delete
fShape.Copy
tWks.Paste

End Sub

But it sounds like just clearing that textbox would be equivalent.

Option Explicit
Sub testme2()
Dim tWks As Worksheet
Dim tShape As Shape

Set tWks = Workbooks("book2.xls").Worksheets("sheet1")
Set tShape = tWks.Shapes("textbox1")

tShape.OLEFormat.Object.Object.Value = ""

End Sub

I used textboxes from the control toolbox toolbar placed on worksheets.