PDA

View Full Version : Event macro does not run until I leave and return to a cell



rackle
09-19-2011, 11:48 AM
I want the autofit merged cell row height code (on both merged and unmerged cells) to run when the user enters text into a cell but the code I have is such that you have to enter text, leave the cell, and then click on the cell again in order for the macro to run. Can I make this less clunky? Here is the code I am using as a selectchange event:

private sub worksheet_change(byval target as range )

Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single
If ActiveCell.MergeCells Then
With ActiveCell.MergeArea
If .Rows.Count = 1 And .WrapText = True Then
Application.ScreenUpdating = False
CurrentRowHeight = .RowHeight
ActiveCellWidth = ActiveCell.ColumnWidth
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth +
MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
End Sub

Thanks!

Aflatoon
09-19-2011, 01:08 PM
Do not use Activecell since that refers to the cell selected after changing the previous cell; you should use Target since that is a reference to the cell(s) that changed.