Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button / Macro won't execute properly
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button / Macro won't execute properly
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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button / Macro won't execute properly
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button / Macro won't execute properly
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button / Macro won't execute properly
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 " 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Command button code does not execute | Excel Programming | |||
Macro won't execute from a Command Button | Excel Programming | |||
How do I execute command from button or hyperlink? | Excel Discussion (Misc queries) | |||
Command button - won't execute twice in sequence | Excel Programming | |||
Programming command button to execute on a different worksheet | Excel Programming |