Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Hide Specific Rows and Display only selected once click on shape

0

Hi team, 
I am new on Excel VBA coding, in my project I have 5 shapes feezed on first row with different names - Project A , Project B , Project C, Project E and Project D all in one sheet. 

i need to assign a macro to each project to show only specific rows and hide all other projects rows with protection on cell as following; 

Project A

once click on shape 1 with name Project A , unprotect active sheet , un-hide entire rows from row("3:15") and hide all other rows . then re protect active sheet again

Project B

once click on shape 2 with name Project B , unprotect active sheet , un-hide entire rows from row("20:27") only and hide all other rows including project A rows  . then re protect active sheet again. 

Project C

once click on shape 3 with name Project C , unprotect active sheet , un-hide entire rows from row("33:65") only and hide all other rows including project A  & B rows  . then re protect active sheet again. 

and so on for other projects. 

by my own coding currently when I click on project B it will show project A or all when i click on all project's shapes. 

is there way to do this?  

Thank you in advance for your support.

excelmacros-vba
MS Office Version: MS 2016
posted by: katshavis on Sat Oct 20, 2018 at 5:36 pm
Answer
Discuss

Answers

0

Excel gives great support to your idea. Before you apply coding learn to achieve the result by applying filters. Then, in the second step, record the key strokes by which you change the filters and assign the macros so created to your shapes.

This will not take care of the protection you also desire. Excel's protection is a complicated animal. Before you try to let code do the unprotecting and reprotecting for you it's best to first fully understand how it works. Perhaps the same thing will happen to you as happened to me: I don't use Excel's protection. However, if you absolutely must protect your sheet, note that you can set your protection to permit filtering. Therefore there would be no need to unprotect it and, hence, no need for reprotection either.

posted by: Variatus (rep: 4889) on Sun Oct 21, 2018 at 2:27 pm
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login