Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 25

Thread: Expand cell height to match textbox

  1. #1
    New Member
    Join Date
    Mar 2018
    Posts
    45
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Expand cell height to match textbox

    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?

  2. #2
    New Member
    Join Date
    Jan 2018
    Location
    Sofia, Bulgaria
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Expand cell height to match textbox

    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.

  3. #3
    New Member
    Join Date
    Mar 2018
    Posts
    45
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Expand cell height to match textbox

    Quote Originally Posted by KKaren View Post
    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

  4. #4
    New Member
    Join Date
    Mar 2018
    Posts
    45
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Expand cell height to match textbox

    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?

  5. #5
    New Member
    Join Date
    Jan 2018
    Location
    Sofia, Bulgaria
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Expand cell height to match textbox

    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)

  6. #6
    New Member
    Join Date
    Mar 2018
    Posts
    45
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Expand cell height to match textbox

    Quote Originally Posted by KKaren View Post
    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?

  7. #7
    New Member
    Join Date
    Jan 2018
    Location
    Sofia, Bulgaria
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Expand cell height to match textbox

    Quote Originally Posted by Mcfg007 View Post
    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.

  8. #8
    New Member
    Join Date
    Mar 2018
    Posts
    45
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Expand cell height to match textbox

    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.

  9. #9
    New Member
    Join Date
    Mar 2018
    Posts
    45
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Expand cell height to match textbox

    Can any one help me with this?

  10. #10
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    13,645
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Expand cell height to match textbox

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •