VBA code to hide/unlide rows based on drop down list

**evakotsi** · 06-30-2012, 07:15 AM

Good afternoon everyone!

I am new to this forum and to VBA actually and I would like your help creating some code in order to hide/unhide rows (and afterwards columns) in an excel workbook.

As you will see in the attached file my goal is to enable the user to choose from a set of (relative) drop down lists and be able to show/hide the respective rows that correspond to those selections.

I have tried creating a code myself (actually google did most of the work) and even though it seems legit, it doesn't appear to have any effect on the workbook..

Can someone who knows what they're doing (because honestly I pretty much don't) help me out please?

Ps. this file in the end will be locked (to protect formulas from users) and then shared.

Thank you in advance,

Eva

**JBeaucaire** · 06-30-2012, 09:45 AM

Macros like Worksheet_Change are event macros triggered automatically by activity on the specific sheet module into which you've inserted it. You placed yours into a standard code module (Module1) which holds macros that are "called" by humans or other macros "on demand" and can function in any sheet.

Move that code into the Sheet1 module and it will begin to work for you.

**evakotsi** · 06-30-2012, 10:57 AM

It was that easy huh?

(whistling)

Thanx a lot for your help!

**ist08** · 06-30-2012, 11:24 AM

http://www.matistvba.com/ FOR BEGINNERS

**evakotsi** · 06-30-2012, 12:46 PM

One more question regarding the code.
Because the sheet will be password protected in order to safeguard the formulas and hiding/unhiding doesn't work on locked cells
can I use some code to unprotect/reprotect the sheet if someone makes a choice in the drop down menu?

Something like:

Please Login or Register  to view this content.

Thanx again!

**JBeaucaire** · 06-30-2012, 01:15 PM

Please Login or Register  to view this content.

If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.

**evakotsi** · 06-30-2012, 01:16 PM

Used the following code instead and it worked:

Please Login or Register  to view this content.

Regards,
Eva

**JBeaucaire** · 06-30-2012, 01:24 PM

A much better solution, but remember that won't hold if you unprotect the worksheet any other way while working on it. I prefer the UserInterfaceOnly method when I give a form out to be used, but it's a little harder to "work" on the sheet and edit/test macros since you have to keep resetting the protection WITH that flag. Keep that in mind. Good job.

**evakotsi** · 06-30-2012, 01:31 PM

I will only use it after the file is completely ready to be given for input.
Thank you very much for your help!

I really appreciate it!

**khesbeats** · 12-03-2012, 10:30 PM

Do you have this tutorial in English? The translation feature is not actually translating all the necessary data. Better yet, if you have a downloadable example, that would be even better! Thanks so much!!