I am new to VB Macros in Excel, but have had little trouble finding what I need and altering macros to suit my specific needs. I have run into one now, however, I cannot get to work.
I have an invoice form with a variable length description. The descriptions are in cells d7 through d34. My problem is that D7 through F7 are merged. When I enter a description that is longer than will fit on one row, it does not expand the row height. I found that to be a known problem in excel and found a macro to do what I need.
What I am trying to accomplish is to enter the description in D7. Upon pressing TAB or ENTER, I want the row to expand in height to display all the text.
Here is the code I am using.
Private Sub WorksheetChange(ByVal Target As Range)
Select Case Target.Address
Case "$d$17", "$D$18"
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single
If ActiveCell.MergeCells Then
If .Rows.Count = 1 And .WrapText = True Then
Application.ScreenUpdating = False
CurrentRowHeight = .RowHeight
ActiveCellWidth = ActiveCell.ColumnWidth
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth + _
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
I get no errors, but it does not work. I entered the code as follows:
- Right click on spreadsheet tab
- Click View Code
- Double click on Sheet1
- Paste and modify code ( all I changed was the range)
- Click File --> Close and return to Excel
When I exit the description cell D7, it remains one row high regardless of the length of the description.
Thanks in advance for any assistance.