Command Button / Macro won't execute properly

[drumsab]

Newbie problem

In a particular worksheet I added a Command Button to execute the macro
below. When I click on the button the Excel "hour glass" appears and then
everything just "hangs", like it's in a loop or something.

If I execute the Macro below using Debug mode or just "Macro Run" from the
Excel toolbar it seems to work just fine.

I'm sure I'm doing something wrong that's very basic but being a newbie to
this stuff I don't know what it could be (I changed the row values while I'm
trying to figure out why it isn't working)?


Sub CommandButton1_Click()

'Turn screen updating and autocalculation off so it doesn't refresh for
every time a row is hidden
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

'Perform loop starting in row 1004 and working "backwards" to row 7, one row
'at a time. For each row, if the value in the cell in column CK is 0, then
'hide the row.
For i = 30 To 7 Step -1
If Cells(i, "ck") = 0 Then Rows(i).Hidden = True
Next i

'Turn screen updating and autocalculation back on for normal operations
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

 

[Dave D-C]

Mine (XL97/Win98) doesn't hang, but it needs
to unselect the button, so maybe add
Range("a1").Select
at the beginning.
D-C Dave

 

[Jim Thomlinson]

That exact code worked fine for me. Do you have any event code that might be
firing??? Try this to see if it goes better...

Private Sub CommandButton1_Click()

Dim i As Long

On Error GoTo ErrorHandler

'Turn screen updating and autocalculation off so it doesn't refresh for
'every time a row is hidden
With Application
..Calculation = xlCalculationManual
..ScreenUpdating = False
..EnableEvents = False
End With

'Perform loop starting in row 1004 and working "backwards" to row 7, one row
'at a time. For each row, if the value in the cell in column CK is 0, then
'hide the row.
For i = 30 To 7 Step -1
If Cells(i, "ck") = 0 Then Rows(i).Hidden = True
Next i

ErrorHandler:
'Turn screen updating and autocalculation back on for normal operations

With Application
..Calculation = xlCalculationAutomatic
..ScreenUpdating = True
..EnableEvents = True
End With
End Sub

 

[SAPkannan]

I am having office 2000 and i copied and pasted your code in my module
under "Sub Button1_Click()"

It is working fine.

Rgds,
Kannan

 

[drumsab]

I made the suggested changes and am still experiencing the "hang".

I'm running Microsoft Excel 2003(11.8146.8132) SP2 if that makes a difference?

The code I tried again was:

Private Sub CommandButton1_Click()
Dim i As Long

Range("a1").Select

On Error GoTo errorhandler

'Turn screen updating and autocalculation off so it doesn't refresh for
every time a row is hidden
With Application
..Calculation = xlCalculationManual
..ScreenUpdating = False
..EnableEvents = False
End With


'Perform loop starting in row 1004 and working "backwards" to row 7, one row
'at a time. For each row, if the value in the cell in column CK is 0, then
'hide the row.
For i = 30 To 7 Step -1
If Cells(i, "ck") = 0 Then Rows(i).Hidden = True
Next i

errorhandler:

'Turn screen updating and autocalculation back on for normal operations
With Application
..Calculation = xlCalculationAutomatic
..ScreenUpdating = True
End With

End Sub