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

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

drumsab <(E-Mail Removed)> wrote:
>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

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

--
HTH...

Jim Thomlinson


"drumsab" wrote:

> 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

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

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








"(E-Mail Removed)" wrote:

> 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
>