Execute code based on cell entry

[rkjudy]

I would like to write the code or create a macro that will execute when the value of a range of cells is greater than null. The macro or code that I would like to execute will UNHIDE a group of consecutive rows. Any suggestions?

[Evagrius]

Hi,

This can be easily done using the worksheet change event. However, more information would be needed to proceed. Below is a sample code from Microsft MVP Smitty. It is relevant for changes to column B.

Please Login or Register  to view this content.

[rkjudy]

Please see attached sheet. Hopefully this will help. As you can see, rows 14-25 are hidden. If there is any numeric entry >0 in cell C11, C12, or C13, I want to UNHIDE rows 14-25. I would also like to reHIDE the cells if the value in any of the 'C' cells above are returned to null.

[shg]

Maybe like this. Code goes in the Build Quote sheet module.

Please Login or Register  to view this content.

[rkjudy]

Wow, it worked perfect. Along those same lines, is it possible to UNHIDE the rows in question just by clicking on any of the 'C' cells, and then hide again if I click off to any other cell?

[shg]

You need to flesh out the logical behavior. What's the intended interaction of that with the other code?

[rkjudy]

Not sure what you're asking, but instead of having to make a positive entry in one of the 'C' cells to execute the UNHIDE, I would like for the UNHIDE to occur just by focusing (clicking) on one of the 'C' cells.

[shg]

Replace the other code with this:

Please Login or Register  to view this content.

[rkjudy]

That didn't seem to work or do anything. Remember, rows 14-25 start out hidden when the workbook is opened. I really would just need the rows to be UNHIDDEN if I click on any of the 'C' cells. I don't really need the rows to return to HIDDEN if I click off the 'C' cells. It's OK if they stay UNHIDDEN.

This is what I tried:

Please Login or Register  to view this content.

[shg]

To reverse the logic, put the word Not before Intersect.

[rkjudy]

This is what I tried and it didn't work either. It seems I need an IF Statement. ie. IF any of the 'C' cells are selected (or get focus), UNHIDE rows 14-25.

Please Login or Register  to view this content.

[shg]

The code goes in the same sheet module where the other code was. If you select C11, C12, or C13, rows 14:25 hide; select any other cell, and they unhide (both referring to the version with the NOT). It does not require any IF.

[rkjudy]

Everything seems to work now, thanks. I do have one more adjustment I need help with. When the code you gave me executes (by clicking in one of the 'C' cells), it DOES, in fact UNHIDE rows 14-25. But, if I click off one of the 'C' cells, rows 14-25 go HIDDEN again. Once they are UNHIDDEN by clicking on a 'C' cell, I'd like for them to stay UNHIDDEN. Can I make a change to keep them UNHIDDEN if I click off a 'C' cell and click to, for instance D45? Here's my code now:

Please Login or Register  to view this content.

Thanks again for your patience.

[shg]

Please Login or Register  to view this content.

[rkjudy]

Works perfect. Thanks for everything.

[shg]

You're welcome. Would you please mark the thread as Solved?