Hiding/Unhiding Rows Based on Dropdown Box

**SubParLlama** · 11-13-2012, 01:38 AM

Hello all, this issue has been stumping me for the longest time and I would greatly appreciate your collective help.

Situation:
I have a worksheet that needs to hide/unhide rows based on a dropdown box (form control). The dropdown box has 3 choices. Depending on the value of the dropdown box, it will hide certain rows. However, the worksheet must be protected because there are formulas within Excel throughout the sheet and as these are being designed for another group, I'd like to have them protected to prevent accidental errors.

Error:
When the sheet is protected and I change the dropdown box (for example from selection 1 to selection 3), I receive a run-time error: 1004. Unable to set the Hidden property of the Range class.

Code:

Please Login or Register  to view this content.

This is the partial code used. The bolded line is highlighted after clicking on debug.

Any help would be great.

Attachment included to help. The error is encountered any time the dropdown box is changed.

**Naveed Raza** · 11-13-2012, 02:04 AM

can u plz provide workbook with unprotected then its more easy to solve ur query.

in that code we can call unprotect to perform assigned work and again we can call protect once the work done.

Thanks - Naveed

**Winon** · 11-13-2012, 02:10 AM

Hello SubParLlama,

Welcome to the Forum.

Try this,

Please Login or Register  to view this content.

Where Sheets("Name" = the actual sheet name, And ("PassWord" = the actual password for the Sheet, if a password does exist.

Also see the "Please consider" note at the bottom of this post.

**SubParLlama** · 11-13-2012, 02:26 AM

Hello Winon. Thanks for your suggestion. I actually tried that and still continued to receive the same error.

To Naveed Raza and everyone, I have included an attachment for reference. Again, thanks for the help.

**Winon** · 11-13-2012, 02:30 AM

And? Where is your attachment?

**SubParLlama** · 11-13-2012, 02:34 AM

Sorry for confusion, please check first post. Attached there.

**SubParLlama** · 11-13-2012, 02:40 PM

I also wanted to note that I tried doing this by recording the actions using the macro recorder. When running only the macro to hide/unhide rather than triggering it from the dropdown box value, there was no error. It was only when using the dropdown box to call the hide/unhide macros that it encountered issues.

All of this when sheet protected to begin with.

**Winon** · 11-14-2012, 01:42 AM

Hello SubParLlama,

Try the attached WorkBook now.

Also see the "Please consider" note at the bottom of this post.

**SubParLlama** · 11-14-2012, 03:09 AM

Thank you very much Winon. That did it. Now time to learn how you did it. Many thanks once again.

**Winon** · 11-14-2012, 12:29 PM

Hello SubParLlama,

You are welcome.

Thank you for the Rep.

You have done a great piece of work, and you don't need to "learn" very hard of how your issue was resolved.

The DropDownBox is linked to Cell G4, and G4 was not "Unlocked" before protecting the Sheet.