Macro hanging up on 2nd use and later.
If you've done a file|print or a file|print preview, you'll see page break
indicator lines on the worksheet.
If you're changing row heights, then excel likes to recalculate where those
lines should be -- and it does this each time you change a row height.
And being in view|page break preview will cause the same problem.
Try putting a couple of lines like these at the top of your routine:
ActiveSheet.DisplayPageBreaks = False
ActiveWindow.View = xlNormalView
Dave H wrote:
I am new to vba and I having an odd issue. The first time I run this
macro it works fine but after the first time it seems to run really
slow or gets caught in a loop. Do I need to clear out memory after use
or is it something else?
Here is the macro any help would be greatly appreciated.
Private Sub CommandButton1_Click()
' Range to Format
Dim FormatRange
' Range Variable for looping
Dim oCell As Range
' RowWidth to set the cell to
Dim Cell_Width As Integer
' Protect the sheet
ActiveSheet.Unprotect
With Application
Calculation = xlManual
MaxChange = 0.001
End With
' Turn Screen Updating OFF
Application.ScreenUpdating = False
' Assign the Range to the FormatRange variable
' Sheet: Explanation M
Set FormatRange = ActiveSheet.Range("Collapse")
' Loop thru the Adjust_Rows range to format the cell to its
' rowheight based on the value in the cell
For Each oCell In FormatRange
' Obtain the cell value which is the Row Height value
Cell_Width = oCell.Value
' Set the Row Height to the cell value obtained above
oCell.RowHeight = Cell_Width
Next oCell
' Turn Screen Updating ON
Application.ScreenUpdating = True
With Application
Calculation = xlAutomatic
MaxChange = 0.001
End With
' Protect the sheet
ActiveSheet.Protect
ExitRoutine:
Exit Sub
ErrorHandler:
MsgBox "ERROR: An error occured in Sub btnExplain_Click: " _
& vbCrLf & Err.Number & vbCrLf & Err.Source _
& vbCrLf & Err.Description, vbCritical, "True Up"
GoTo ExitRoutine
End Sub
--
Dave H
--
Dave Peterson
|