Help with Autofit Row Height Macros

[Lostguy]

Hello!

I need to have Excel 2003 automatically adjust row height for
protected cells with text. Here are the two codes I have. Which is
better, Worksheet Calculate or Selection Change? Also, what is the
difference between Range("A28:32) and Rows ("28:32")? Basically, is
one of these codes better than the other? s there anything you would
recommend to increase their reliability? Thanks! VR/Lost

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
ActiveSheet.Unprotect "password"
Range("a28:32").EntireRow.AutoFit
ActiveSheet.Protect "password"
Application.EnableEvents = True
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo enditall
Application.EnableEvents = False
Rows("28:32").AutoFit
Rows("53").AutoFit
enditall:
Application.EnableEvents = True
End Sub

 

[Dave Peterson]

I'd drop the _selectionchange event from consideration. I know that I select a
range much more often than I do something that causes a recalculation.

But if the changes were made by the user typing a value (not because of a
formula re-evaluating), then I'd use Worksheet_Change, too.

For your specific questions...

Range("a28:32") doesn't work for me at all. I bet you meant Range("a28:a32")???

If that's true, then I think the choice is completely yours--which do you find
easier to understand?

I think I would usually use range().entirerow, but it depends on what comes to
mind <bg>.

I may even use:
rows(28).resize(5)


But I wouldn't use Activesheet and I'd qualify my ranges:

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
me.Unprotect "password"
me.Range("a28:a32").EntireRow.AutoFit
me.Protect "password"
Application.EnableEvents = True
End Sub

The Me keyword represents the object that owns the code--in this case, it's the
worksheet that's being recalculated.

You do have a significant difference in your procedures, though. One unprotects
and reprotects the worksheet. The other has some error handling (if the
worksheet is protected???).

If I know the worksheet is protected (and its password), then I'd use the
..unprotect, .protect version.

If I don't know if the worksheet is protected (and I won't know the password),
then I'd check the protection status and give a message.

Option Explicit
Private Sub Worksheet_Calculate()

If Me.ProtectContents = True _
Or Me.ProtectDrawingObjects = True _
Or Me.ProtectScenarios = True Then
MsgBox "Please unprotect the worksheet..."
Else
Application.EnableEvents = False
Me.Range("a28:a32").EntireRow.AutoFit
Application.EnableEvents = True
End If

End Sub

If the msgbox is too irritating (it shows up after each calculation), you could
beep or do something less irritating.

 

[Don Guillett]

You might think to use a worksheet_change event but if the sheet is
protected how are you inputting larger text into the cell(s)???