none
CRASH: ActiveSheet.Rows.Hidden RRS feed

  • Question

  • Hello,

    I am extremely new to vba. I am trying to execute a very simple command, and suddenly Excel freezes up as if it is attempting to run an extensive loop, and I have to end the program through task manager. The code is as follows:

    Private Sub Worksheet_Calculate()
        If Sheet14.Cells(4, 4) = True Then
            Sheet14.Rows("119:119").Hidden = True
        End If
        
    End Sub

    The error occurs no matter what type of method I am using as long as I am hiding rows. I have tried hiding different rows, and the same thing happens. Any thoughts?

    Thanks

    Saturday, January 5, 2013 4:41 PM

All replies

  • In some circumstances, hiding or unhiding rows can cause the worksheet to be recalculated, which causes Worksheet_Calculate to run, etc.

    You can prevent this by adding a line

        Application.EnableEvents = False

    above the line that hides the row, and

        Application.EnableEvents = True

    below it. But I have some questions:

    Does the user enter TRUE or FALSE in cell D4?

    If so, it would be better to use the Worksheet_Change event, and to check whether D4 has changed.

    If, on the other hand, D4 contains a formula, which cell or cells contribute to the result of the formula?

    Do you only want to hide row 119 if D4 is True? Or should it also be unhidden if D4 is False?


    Regards, Hans Vogelaar

    Saturday, January 5, 2013 5:09 PM
  • Excellent, I will give that a shot and let you know. I have to run for the evening- I will post tomorrow. And, no the user does not enter the cell information. A checkbox dictates the boolean value. This code is actually going to be run on a set of check boxes in the sheet. I am working on other ways to achieve my goal. But nothing has come up so far...

    Thanks you for the reply- I'll update tomorrow :)

    Saturday, January 5, 2013 5:33 PM
  • Sorry, I did not answer your last question. I intend to add code for unhiding the row if the the value is true, but I will do that after I get this part sorted... Thanks again!
    Saturday, January 5, 2013 5:36 PM
  • I guess it was some kind of bug, or a mistake I couldn't find. I moved to an older copy of my spreadsheet (almost exactly the same), and just copied the code over to it, and it worked fine...

    Thanks again,

    /G

    Sunday, January 6, 2013 10:40 AM