-
Event macro does not run until I leave and return to a cell
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:
[VBA]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[/VBA]
Thanks!
Last edited by Bob Phillips; 09-19-2011 at 01:10 PM.
Reason: Added VBA Tags
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules