Expand cell height to match textbox

[Mcfg007]

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]

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]

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]

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]

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]

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]

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]

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.

 

[Mcfg007]

Can any one help me with this?

 

[MickG]

Perhaps something like this:-

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