Automatically hide/unhide rows based on cell value

[itsawayoflife]

Hello everyone, I am not positive that this is the right section to post in, or that VBA is even the right/best way to go with solving this problem, but I'll give it a try.

I am wondering if it is possible to write in staff names (up to 80) on Staff!, but have the rows 23-80 hidden and only unhide row 23 when a staff name is typed into cell A22. Row 24 would unhide when there is a staff name typed in cell A23, and so on for all the rows 23-80. When this happens on Staff! sheet, it should also do the same thing in all the other sheets (Jan-Mar, Apr-June, July-Sept, Oct-Dec) and triggered by what happens with the staff names on the Staff! sheet.

The Staff! sheet has the calendar on it (which is why I can't hide the entire row until row 23 as it would hide part of the calendar), but on all the other sheets there is nothing hindering all the rows from being hidden until the previous row has a staff name in it. Is it possible to do this?

I am hoping for all this to be automatic without needing any operator input (like buttons or refreshing or filtering), but to have a code or something running all the time to watch what happens with the staff names and then trigger the hiding or unhiding.

Please find example document attached.

Thank you!

Mike

[BadlySpelledBuoy]

Paste the below code into the Staff worksheet module then hide rows 23:80 on each sheet and give it a go.

Please Login or Register  to view this content.

BSB

[WideBoyDixon]

I went a slightly different way but still using the Worksheet_Change() event in the Staff sheet. This also copies the staff names to the other sheets. See attached.

WBD

[itsawayoflife]

Wow. Thank you so much! My little heart is happy now. :-)

Cheers,

Mike

[itsawayoflife]

Hello again,

As I have been playing with the file I noticed two things which I'm wondering if they could be adjusted.

1) the hide/unhide does not allow row 80 to unhide... it stops at 79.
2) when I try to protect each sheet (so people cannot mess with the formulas) there is an error and it cannot hide or unhide the protected rows.

Is there anything which can be done to solve these?

Please see the new reference file attached.

Thank you!

[BadlySpelledBuoy]

Which of the solutions provided are you using?
Each will need amending in a different way.

BSB

[itsawayoflife]

I am using the solution from WideBoyDixon. I think that was what you were asking...

[WideBoyDixon]

I tweaked the solution a little and I've attached an updated workbook. If you protect the sheets, the code will fail because you can't hide/unhide the rows on a protected sheet.

WBD

[itsawayoflife]

So perfect! Thanks so much for that. It was really helpful the little 'explanations you added into the coding. I found I learned a lot through that.

Thanks for your help!

[BadlySpelledBuoy]

For the sake of completeness, it's worth mentioning that you can have the sheets protected and still hide/unhide rows via code.

If you put the below in the ThisWorkbook module it will fire when the workbook is opened and protect each worksheet.
The 'UserInterfaceOnly:=True' part allows protected sheets to be manipulated by code but not by the user.

Please Login or Register  to view this content.

It can be adapted easily to only protect certain sheets if required.
This for example will protect all sheets other than the one named 'Staff'.

Please Login or Register  to view this content.

BSB

[WideBoyDixon]

For the sake of completeness, it's worth mentioning that you can have the sheets protected and still hide/unhide rows via code.

If you put the below in the ThisWorkbook module it will fire when the workbook is opened and protect each worksheet.
The 'UserInterfaceOnly:=True' part allows protected sheets to be manipulated by code but not by the user.

Well I never knew that. Thanks for the information

WBD

[BadlySpelledBuoy]

Learn something new every day round here mate

BSB