I'm trying to have Sheet 1 run a macro (code below) that automatically
resizes a merged cell in Sheet 2 when I select new selection from a
drop down box in Sheet 2. My inclination is that I should be using a
Worksheet Change function but I'm unsure how to write the code to call
this macro from there. Also, a problem I don't want to run into is
that the cell on Sheet 1 will not be active since I'm changing it via a
drop down on Sheet 2. Any help appreciated.
--John

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