Macro hanging up on 2nd use and later.

[Dave H]

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]

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

[Dave H]

That does the job. Thanks for your help.


--
Dave H