Text Box

[bparker]

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

 

[Daniel CHEN]

For text box:

In the format-alignment -> check the box for "automatic size"


--
Best regards,
---
Yongjun CHEN
=================================
XLDataSoft - Data Analysis Expert, Excel/VBA Specialist
- - - - www.XLDataSoft.com - - - -
Free Excel-Based Data Processing Tool is Available for Download
Free Excel / VBA Training Materials is Available for Download
=================================

 

[Guest]

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]

Thanks for your help! The automatic size thing is nice, but it makes i
all one very long line! Can I set a maximum horizontal size so that i
will automatically size vertically, adding lines where more ar
needed???
Bra

 

[Guest]

Sorry, not that I could figure out. In the code, I just set the horizontal
distance I wanted. Then, I found the width of the one very long line. From
there, I calculated the approximate verticle size. So no, I don't know how
to do it automatically, but the code I displayed does what I described above.