Brad,

If the automatic size button was the answer to your question, ignore the
rest of this.

I worked all day on something similar. (It shouldn't have taken all day,
but I'm just beginning.) The following code resizes what is typed into a
text box, pastes it in another cell, and then goes back and reformats the
original text box it came from. This is probably more than you are looking
for, but reworking and naming some of the code may help you out.

Dim variable

Sub ProdResSummaryTextBox()

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''
'This fills in and formats the summary text box on the Prod. Res. tab'
'and copies and formats it to the Summ. tab. '
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''

'Finding out the appropriate dimensions for the text box
Sheets("Prod. Res.").Activate
Sheets("Prod. Res.").Shapes("ProdResTextBox1").Select
Selection.Copy
With Selection
.AutoSize = True
variable = .Width
End With


''''''''''''''''''''''''''''''''''''''''''''''''
'Changing the dimensions of the Summ text box '
''''''''''''''''''''''''''''''''''''''''''''''''
Sheets("Summ").Select
''''''''Testing'''''''''''
'If ActiveSheet.Shapes("SummTextBox1").Value Then
'ActiveSheet.Shapes("SummTextBox1").Select
'Selection.Delete
'End If
''''''''''''''''''''''''''''''
ActiveSheet.Range("A46").Select
ActiveSheet.Paste

With Selection
.Name = "SummTextBox1"
.ShapeRange.LockAspectRatio = msoFalse
.ShapeRange.Height = (variable / 650) * 15 + 15
.ShapeRange.Width = 650#
End With


'''''''''''''''''''''''''''''''''''''''''''''''''
'Changing the dimension on the original text box'
'''''''''''''''''''''''''''''''''''''''''''''''''
Sheets("Prod. Res.").Activate
Sheets("Prod. Res.").Shapes("ProdResTextBox1").Select

With Selection
.AutoSize = False
.ShapeRange.LockAspectRatio = msoFalse
.ShapeRange.Height = (variable / 650) * 15 + 15
.ShapeRange.Width = 650#
End With

End Sub


"bparker" wrote:


Can you make a text box resize automatically when you type in more info
than can fit? What about using a large cell instead of a text box?
Can you make it resize automatically to wrap and fit it all into view?
Thanks,
Brad


--
bparker
------------------------------------------------------------------------
bparker's Profile: http://www.excelforum.com/member.php...o&userid=34940
View this thread: http://www.excelforum.com/showthread...hreadid=546820