moving/copying text boxes

[eds000]

I have a text box (253) from the DRAW menu which contains 3 lines (warning message) which is deleted if the user enables the macro when opening the file

I need to put the textbox in the same position on the sheet when the user closes the file so whenever the user opens the file they are forced to enable the marco.

I tried using the text box in the control box but I can't control it and I need to have the text box containing 3 lines of data. Any easy way of doing this?

 

[Gary McMaster]

You might be able to save yourself some trouble if you make the text box invisible rather than deleting it.

ActiveSheet.Shapes("Text Box 253").Visible = False 'Change text box name as required.

ActiveSheet.Shapes("Text Box 253").Visible = True 'Change text box name as required.

If you want the text box to appear in exactly the same place between sessions you will have to record its position when the book is closed or put it in the correct position before the book is closed (and saved). There are a number of ways you could store its position.

You could use something like the following to make sure the text box is at least visible if the exact position is not required.

Gary

Public Sub Test()

Dim sAddress As String
Dim oTextBox As Shape

'Roughly center of visible range
sAddress = ActiveWindow.VisibleRange.Cells(ActiveWindow.VisibleRange.Cells.Count / 2).Address

'Instead of above, calculate top left of text box using window size.
'MsgBox ActiveWindow.Height
'MsgBox ActiveWindow.Width

Set oTextBox = ActiveSheet.Shapes("Text Box 1")

oTextBox.Left = ActiveSheet.Range(sAddress).Left
oTextBox.Top = ActiveSheet.Range(sAddress).Top
        
oTextBox.Visible = msoTrue
        
End Sub