Macro does not run automatically

[greatday2882]

I have a workbook that has 11 tabs. 1 is a summary page, and the other 10 are the variable sheets. The 10 variable sheets have check boxes for about 30 different values per sheet to choose from. The summary sheet shows all (roughly 300 items) of the data from all 10 of the variable sheets. Column E in the summary sheet shows an "x" when the box is unchecked in the variable sheets, and shows "Have" when it is checked. I wanted all the rows where the value in the E column was "Have" to automatically hide the entire row. I inserted the following as a sheet module:

Please Login or Register  to view this content.

That worked perfectly. But after the initial hiding of the rows with the Value of "Have" in the E column, i can check boxes in the 10 variable sheets, the summary page with the macro will still update from "x" to "Have", but in order to have it auto-hide, i have to make any cell active, then hit enter and the macro will work. I am an extreme VBA noob. Is there anything that seems wrong with what I've done? Thank you for the help!

[TMS]

Do you have a global variable somewhere called Have?

Please Login or Register  to view this content.

If you don't, this will check the cell value for being null/empty as the variable, Have, is not defined.

So, in what way did this work perfectly first time around?

Usually, a worksheet change event monitors changes to specific cells. What you have here will activate whenever you change any cell anywhere on the sheet. And, when it activates, it will check every cell in column E for the value in the variable (undefined) Have.

Regards

[greatday2882]

All of the cells in the E column contain an IF function, pointing at their corresponding line in the other sheets. So the first row on the summary sheet, the formula in cell E2 is:

Please Login or Register  to view this content.

When I go to sheet 2, I click the check box on Row 5, and in cell E5, it will toggle between TRUE and FALSE from the checkbox. Then the summary sheet, in turn, toggles between "x" and "Have" in cell E2.

As far as how it worked perfectly, on the summary sheet (sheet1), after I added the VBA code from my first post, I closed the VBA editor, and all the rows that had "Have" in the E column were hidden, just like i want. But now, lets say cell E20 is still an "x" because I have not clicked the checkbox on sheet2. I switch over to sheet2, hit the check box, switch back to the summary sheet, cell E20 now says "Have", but row 20 is not hidden. If i make any cell on the summary sheet active, then hit enter the macro will then hide row 20. I'm wondering if its possible to have the macro hide the updated lines when i switch between sheets, like once i click the check box, go back to the summary page, the corresponding line to the check box I just clicked is automatically hidden, without me having to do anything. Thank you again for the help

[TMS]

That's the flaw in the process. The worksheet change event is only activated on manual input on the sheet being monitored, not a changed value from a formula.

Sounds like you could use a worksheet activate or deactivate rather than a worksheet change event. But that would be dependent on the sheet being selected or deselected, which is Ok assuming you want it to change when you look at it.

Incidentally, Excel 2007 has 1048576 rows ... so that's what you loop through if and when this event is activated. Does it take a while to respond?

Regards