LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Member
 
Posts: 54
Default 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
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 04:26 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"