copy Textbox to other sheets

[neodjandre]

Hello,

I have 'Sheet1' which contains a textbox.

I need to find a way to copy this textbox to all other sheets and make its size the same as the used range in each sheet.

Is this something that can be achieved?

thanks in advance,
Andy

 

[micro-blades]

Perhaps this can get you started:


Code will create a new text box on every sheet the same size and location as the used range. It doesn't copy the textbox on sheet1 but you can add the formatting to the code so they all are similar to it.

Dim sht As Worksheet

For Each sht In ThisWorkbook.Worksheets
    With sht
        .Select
        If Not .UsedRange.Cells.Count > 0 Then GoTo Continue
        .OLEObjects.Add(ClassType:="Forms.TextBox.1", Link:=False, _
            DisplayAsIcon:=False, Left:=.UsedRange.Left, Top:=.UsedRange.Top, _
            Width:=.UsedRange.Width, Height:=.UsedRange.Height).Select
    End With
Continue:
Next
End Sub