VBA to resize a range to fit the textbox.

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,356
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

Well-known Member
Joined
Mar 11, 2015
Messages
4,698
Office Version
365
Platform
Windows
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 ?
 
Last edited:

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,356
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

Well-known Member
Joined
Mar 11, 2015
Messages
4,698
Office Version
365
Platform
Windows
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"

Code:
    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
 
Last edited:

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,356
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

Well-known Member
Joined
Mar 11, 2015
Messages
4,698
Office Version
365
Platform
Windows
Glad you are sorted
(y)
 

Forum statistics

Threads
1,082,316
Messages
5,364,483
Members
400,802
Latest member
RichBRich

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top