Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Macro hanging up on 2nd use and later.
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
|
|||
|
|||
That does the job. Thanks for your help.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|