how do I create event-activated macros that check every cell?

Is this any good for you

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = FalseSub AutoFitMergedCellRowHeight()
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single
Dim cell As Range
Dim sh As Worksheet

If Target.MergeCells Then
With Target.MergeArea
If .Rows.Count = 1 And .Cells(1).WrapText = True Then
''<<.Cells(1) added
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
ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"JLC" <JLC.1z8ijy_1133227802.41@excelforum-nospam.com> wrote in message
news:JLC.1z8ijy_1133227802.41@excelforum-nospam.com...
>
> I'm so thankful for Jim Rech's code* (and those who directed me to it)
> that allows auto-expansion of merged cells, but I have a *shared
> workbook *with at least *10 sheets*, each of which has more than *40
> different merged cells *that may need auto-expansion. Because there are
> so many merged cells, not to mention Excel-phobic users typing into
> them, I want to avoid personally running this macro for each cell that
> needs expansion. Unfortunately, my _very_ rudimentary VBA skills are of
> no help.
>
> Given that, I have several questions:
>
>
> - Can I have the macro run automatically for each sheet, checking all
> the merged cells (or simply all cells), when the user saves or exits?
>
> - Can I have the macro check every single cell in the workbook? I'd
> like to run this version manually.
>
> - Can the macro activation event be when the user has finished
> entering text and moves to a new cell whether by
>
>
> - tabbing,
> - hitting enter, or
> - clicking on the next cell they want to modify?
> If so, does anyone know how expensive this would be in terms of
time
> and memory? All my users are accessing the workbook remotely and
> already have a good deal of lag due to outdated equipment.
>
>
> *'Jim Rech's AutoFitMergedCellRowHeight Code'
> (http://tinyurl.com/aknxy)
>
>
> --
> JLC
> ------------------------------------------------------------------------
> JLC's Profile:
http://www.excelforum.com/member.php...o&userid=28014
> View this thread: http://www.excelforum.com/showthread...hreadid=488929
>

That works exactly how I'd hoped except that it changes the width of the first column in the merged cell. Can we change the code so that it doesn't alter column widths?

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)

I can't wait until I'm able to do some of this on my own... ; )

Nevermind. When I turn on sheet protection and allow formatting of cell, row, and column, the macro works perfectly. Of course, I don't want my users actually doing any formatting. If they figure out they can change things, there's no telling what will happen. Any suggestions about how to skirt this issue?

Thanks so much!
jc

If you turn full protection on with a password, and then in the code, turn
it off, do your stuff, and then turn it back on.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JLC" <JLC.1z9owz_1133282709.2261@excelforum-nospam.com> wrote in message
news:JLC.1z9owz_1133282709.2261@excelforum-nospam.com...
>
> Nevermind. When I turn on sheet protection and allow formatting of cell,
> row, and column, the macro works perfectly. Of course, I don't want my
> users actually doing any formatting. If they figure out they can change
> things, there's no telling what will happen. Any suggestions about how
> to skirt this issue?
>
> Thanks so much!
> jc
>
>
> --
> JLC
> ------------------------------------------------------------------------
> JLC's Profile:
http://www.excelforum.com/member.php...o&userid=28014
> View this thread: http://www.excelforum.com/showthread...hreadid=488929
>