VBA to resize a range to fit the textbox.

[tonywatsonhelp]

Hi Everyone,

I have a problem,
I need people to be able to copy and paste text from Word into excel, I have encounted a number of problems doing this as the people doing the cut and pasting are all at the lower end of the gene pool.

So after many hours of testing the only solution I could find that works perfectly is to have a textbox in excel that they can paste their word text into.
nnow this works great but I have another problem. the area height is not fixed.

I found a property in the textbox that will resize it to fit text and whilst this is not ideal it might be ok.
however I need the Sheet to respond to the change in text size.

So heres what I need

Range C110:G100 (are merged)
Textbox fits within area. (textbox is called "words 1")

when someone pastes into textbox "Words 1" resize text box height to fit all text.
Then resize

Range C110:G100 row height to be same size as textbox so textbox still fits inside.

please help if you can
Thanks
Tony​

 

[Yongle]

as the people doing the cut and pasting are all at the lower end of the gene pool

Does the above adjunct add anything to the post

when someone pastes into textbox "Words 1" resize text box height to fit all text.
Then resize Range C110:G100 row height to be same size as textbox so textbox still fits inside

Q1 Should each of 11 row heights in Range C110:G100 be adjusted to an eleventh of textbox height ?


Q2 Have you tried right-clicking textbox and checking the box Resize shape to fit text ?

 

[tonywatsonhelp]

Hi Yongle,

sorry mistype range should be

Range C​

110

:G​

110 so just one row.

Yes, right-clicking textbox and checking the box ​

Resize shape to fit text

? works for the text box but not the cell range so happy to use this as part of it if better.

Thanks

Tony
​

 

[Yongle]

One line of VBA is required to make the row height match textbox height
- the VBA could be put behind a button or triggered by a worksheet event etc
- unfortunately updating a standard textbox is not a pre-configured "event"

    ActiveSheet.Rows(110).RowHeight = ActiveSheet.Shapes("Words 1").Height

Consider using an active-X textbox which has a range of pre-configured events available for more flexibility

 

[tonywatsonhelp]

Hi Yongle,
This is excellent thank you.
Its no problem getting the macro to trigger I have already though of that one. so that all i needed thank you.
Tony

 

[Yongle]

Glad you are sorted