Expand cell height to match textbox

Mcfg007

New Member
Joined
Mar 10, 2018
Messages
45
How can I get the cells to expand with the size of a texbox. I am entering text and the Activex texbox is expanding with Multiple lines accordingly to the text, but after a period goes over other fields, How can I get the field cells to expand?
 

KKaren

New Member
Joined
Jan 13, 2018
Messages
33
I believe you can use the wraptext property, for example

Range("A1").WrapText = True

but this will increase the height of the row related to the relevant cells.
 

Mcfg007

New Member
Joined
Mar 10, 2018
Messages
45
I believe you can use the wraptext property, for example

Range("A1").WrapText = True

but this will increase the height of the row related to the relevant cells.
Sorry, I don't follow. Where do I place that code? My Activex Texbox is on top rows D-J 18 & 19
 

Mcfg007

New Member
Joined
Mar 10, 2018
Messages
45
I read somewhere about this code but not too sure how to go about it as I see no reference to specific sheet or textbox.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count = 1 And IsEmpty(Target) Or Not IsEmpty(Target)
Then Range("A9").RowHeight = ActiveSheet.Shapes("Scope_IL_Definition_TB").Height
End If
End Sub


can anyone help?
 

KKaren

New Member
Joined
Jan 13, 2018
Messages
33
Sorry...I didn't understand your question initially.

The code you have pasted, as I understand seems to change the height of the 9th row depending on the height of some shape, if a single cell is selected, so I believe this won't work for you.

I tried to expand the size of an ActiveX textbox but the text appeared awful on my PC. I hope somebody will be able to help you as I'm not a big fan of using ActiveX controls as some events do not exist (for example AfterUpdate)
 

Mcfg007

New Member
Joined
Mar 10, 2018
Messages
45
Sorry...I didn't understand your question initially.

The code you have pasted, as I understand seems to change the height of the 9th row depending on the height of some shape, if a single cell is selected, so I believe this won't work for you.

I tried to expand the size of an ActiveX textbox but the text appeared awful on my PC. I hope somebody will be able to help you as I'm not a big fan of using ActiveX controls as some events do not exist (for example AfterUpdate)
Yes I see what you mean, I am only using Activex Box because they don't have the 256 limit on characters like normal boxes do.

That code was something I saw elsewhere, just wondering if it could be adjusted and it seamed that was written to adjust the rows depending of the size of the textbox.

My question is, I have inserted an Activex Texbox which is covering two from rows 18 and 19 from D to J. I wanted row 19 to expand down if the textbox expands with the amount of text in it.

Appreciated if any one could help please?
 

KKaren

New Member
Joined
Jan 13, 2018
Messages
33
Yes I see what you mean, I am only using Activex Box because they don't have the 256 limit on characters like normal boxes do.
I don't know of such limit...In fact, in most of the MS Form control textboxes I am using at the tools at work, there are much more than 256 characters in them.
 

Mcfg007

New Member
Joined
Mar 10, 2018
Messages
45
Yes, I mean to be then linked to another text box. The text is enter on one sheet texbox which then shows on a separate sheet on another textbox. If is a normal text box only shows up to 256 characters of the first box. hence why using activex boxes. the issue obviously now is on my impute txt box I have cursors to move up and down to see text, but on my other sheet I need the box to expand as it will be printed, therefore need the cell to expand to be able to see information that is below.
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Perhaps something like this:-
Code:
Private Sub TextBox1_Change()
With TextBox1
    .AutoSize = True
    .MultiLine = True
    .WordWrap = True
    .TopLeftCell.RowHeight = TextBox1.Height
    .Width = 100 'Set as required
End With
End Sub
 

Forum statistics

Threads
1,081,838
Messages
5,361,614
Members
400,642
Latest member
tekster23

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