Last week a customer sent me an Excel file which was set up as a basic form to capture information. As you can see from the screenshot below, the purpose of the form was to capture information about product complaints. The height of Row 8 had been increased and B8 was where the user would type their comments.
The customer wanted a way to automatically amend the height of row 8 depending on how much text was in B8. I wrote a short VBA procedure to do this:
Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveCell.Row = 8 Then
ActiveCell.EntireRow.AutoFit
End If
End Sub
The code, which is stored in the Module for the actual worksheet, sets the height of row 8 to fit the content of the cell (it is the same as manually applying the Autofit command).
To get the code to run automatically, I attached it to the “Worksheet_Change” event, which runs every time the content of ANY cell in the worksheet is changed. However, to make sure that it only changes the height of row 8 when the content of row 8 is amended, I wrapped the line of code within an IF statement.
I also applied the following settings:
- Turn OFF the “Move Selection After Enter” feature – you will find this in Excel’s Options dialog box, although it’s in a slightly different location depending on the version of Excel you are using.
- Apply Top Alignment to B8 so that when the user types their comments, the text starts at the top of the cell, rather than the bottom, which is Excel’s default. I also applied Top Alignment to A8 so that the label/heading is aligned with the comment text.
- Enable Wrap Text on B8 so that when the user types their comment, when the text reaches the right hand edge of the cell, it wraps round within the cell.