Conditional Hide/Unhide if Cells Change

[engineer85]

I am trying to hide/unhide rows when cell ("C9") changes. Cell "C9" changes based on selections from drop boxes in cells "C5", "C7" and "C8". It's been a while since I've used VBA in Excel so I was browsing online and came up with the following:

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

Please Login or Register  to view this content.

This works the first time cell "C9" changes but when it changes again (based on the selection in cell "C5", "C7" and "C8"), it does not hide/unhide the cells. I think I have to use Select Case or a loop or something but I haven't done this in a long time.

Any help is appreciated!

Thanks,
Nichole

[walruseggman]

Looks like you need a Worksheet_Change event. Something like:

Please Login or Register  to view this content.

This has to reside in the object for the sheet where the change is happening, not in the Workbook object.

[engineer85]

Thanks for the help! Sorry I am a beginner. For it to reside in the object for the sheet, would this be under VBAProject-->Microsoft Excel Objects-->Sheet2(Quantities-Alt1)? I tried this and it does not seem to work. Does it matter that I am not physically changing the value in cell "C9" (it is a function of the selection of cells "C5", "C7" and "C8")?

Thanks again,
Nichole

[walruseggman]

Yes, that's the correct location.

Not sure on your second question. By what method specifically is the value in C9 changed. If you post the specifics I can play with it.

[engineer85]

Thanks. Sorry, I will try to post specifics (I'll use letters and numbers to simplify, unless there is an easier way)

Cell C5=drop down list with choices: (A, B, C, D, E, F, G, H, I)
Cell C7=drop down list with choices: (1, 2, 3)
Cell C8=drop down list with choices: (4, 5, 6)
Cell C9= IF(AND(C5=F, C7=1, C8=4)=TRUE, Condition #2,
IF(AND(C7=1, C8=4)=TRUE, Condition #1,
IF(AND(C7=2, C8=5)=TRUE, Condition #2,
IF(AND(C7=3, C8=6)=TRUE, Condition #3, "ERROR"))))

Then I'm using above VBA to do following:
If C9=Condition #2, then hide Rows A16:A22
Otherwise, hide Rows A11:A15

So if Cell C9 changes based on the selection in cells, C5, C7 or C8, I would need to hide/unhide the rows specified above.

[walruseggman]

I have a suspicion that there is a better way to do this. Regardless, try this code:

Please Login or Register  to view this content.

[engineer85]

That did it!!! I'm sure there is a better way but my knowledge on this stuff is limited. Thank you!!