VBA to resize row to the same as tallest text box in that row

  • I need help to make rows expand to fit the largest text box within that row. I found the code below online, it works perfectly on the spreadsheet it came on but it doesn't work on my spreadsheet.
    Any help will be appreciated.
    Thanks in advance :)


  • Re: VBA to resize row to the same as tallest text box in that row


    Hi Paulod,
    Next time, please use the CODE tags around your data, so it's easier to read.
    Have you changed the named of your text boxes?

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: VBA to resize row to the same as tallest text box in that row


    No, I haven't and I didn't realise I'd have to name each text box - the spreadsheet will have hundreds of them when complete!

  • Re: VBA to resize row to the same as tallest text box in that row


    Hi Luke, thanks for helping me, you probably already figured out that I'm new to VBA.


    When I run the code I get Run - time error '1004' Application - defined or object defined error and when debugging this part of the code highlights.


    .Rows(mht(i, 2)).Rowheight = mht(i, 1)

  • Re: VBA to resize row to the same as tallest text box in that row


    Hi Paulod,


    You don't necessarily "have" to change your code, but I notice that's it going thorugh all the shapes and detecting those whose Name starts with "TextBox", so if you had already changed the name to something other than default, the code won't see them.


    Are any of the text boxes VERY large? There is a row height limit...I suppose if the shape is taller than max allowed row height, that would cause an error.

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: VBA to resize row to the same as tallest text box in that row


    I worked out that by adding parenthesis to the sheet name, i.e. With Sheets("Sheet7") fixed the problem.
    The reason for text boxes is to allow better formatting, in this case bullet points and indenting.
    If anyone can help me use the colour of source cells to format a 3D graph you'd be my new best friend. I manually do this and each time the data changes I have to update the appropriate column.


    [ATTACH=CONFIG]60857[/ATTACH]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!