Make a group of cells open upon click?

Hi, Try this as an Example:-
Write in "A1" :- School1, in "A11 :- School2, and in "A21":- School3.
Now Name the ranges in between each Name i.e Range ("A2: A10") = Name :- School1,
Repeat for other 2 Ranges i.e Range("A12:A20") = Name:- "School2 and Range"A22: A30") =Name :- School3.
To enter code in VB Editer, Right Click sheet Tab, Select "View Code", VB Windowe appears. Paste code below into VbWindow.
Close Vb Window.
To run code, Click anyone of the 3 cells with the School Names, On selection the other 2 Range/Rows should be Hidden. Repeat for other "Schools"

Please Login or Register  to view this content.

Regards Mick

Great, thanks for the help, Mick. Just two things. When I do this and click one of the cells, all of the ranges open up (Example, I click A11 and A2:A10, A12:A20, AND A22:A30 open up. I want each cell clicked on to only open the range directly below it...is there a way to edit this code to do that?

Also, is there an easy way to make it close back up afterwards?

Thanks again, we're definitely on the right track with this!

smalls

Hi, Not quite sure what's happening there !!. Before you run the code all rows should be showing.
Then for example, when you click "School2", First of all all the Ranges show. Then ranges for School1 and School3 will be Hidden,But the row name for "School1" (in A1) will be seen above the Range which is Visible (School2) and the the row name for "School3" will be seen at the Bottom of the Visible Range (School2).
Is this not Happenng???
Mick

OK...got it to work! I think I'd been naming the ranges wrong (using the little box in the upper left). When I clicked on "define name" and did it that way, it worked.

Is there a way to make it close back up again when clicking on the cell when the range below is open?

Thanks again Mick!

smalls

Hi, Have a look at the attached File, If you want any mods , call back.
To view the Sheet codes inthe file , Right click sheet tab, Click "View Code" or just Click "Alt +F11".
I've added some extra code, So that if you Double click "B1" all the ranges are Visible, Double Click again, All ranges are Hidden.
Regards Mick

Thank you Mick!

Hi, Have a look at the Second Sheet of the Attached File, I think that's what you want !!!
Regards Mick

Hi Mike, I've just Downloaded my posted file "School_1.xls" that I sent previously and it works as I sent it and as you would expect it to, I also ran the code "Msgbox Thisworkbook.name" without any problems ,so what do you think could be causing the problem.
What happens if you down load the Original File I posted "School.xls". The second file is exacly the same but with an added sheet that's almost identical.
I wonder if "smalls" if having the same problem ???
Regards Mick

If it is of any help,
I have opened and ran successfully both files, in both Excel 2003 and 2007.

Hi, If you actually want it in a "Double Click Event" then Here'sthe Code:-

Please Login or Register  to view this content.

Regards Mick

Hi Mick,

Thanks again for your response. This is my first time writing a code in Visual Basic...can you just clarify which are the parts of your code which I will need to change in order to customize it to my sheet? I haven't been able to make it work for my particular spreadsheet, and I'm sure there's some piece of the programming which I should replace and am not.

Thank you very much for all your help on this--if I can get it to work it will make this tool really great!

Cheers,
smalls

Hi, Please see line numbers in code for relating to remarks.
Line (1) :- shows the individual; addresses of a some cells, These are the cells with the "Names" of the "Named Ranges " that you want to hide, You can add or delete to these address for as many named ranges that you want to perform this action on.
NB:- The Names in these Various cells must Have exactly the same name as the range of cells they refer to.:- i.e "School1" ( no Inv/commas) must be spelt exactly the same as the Range Named :- "School1".
Line (2) Refers to the Range(Target). The target is the cell you select. If that cell is one of the cell addresess in the code line above then, Then by selecting that cell, you are carrying out an action relating to the Named range that the target cell refers to. So the range referred to in line (2) has been given the property "Hidden", and the code will try to hide this range. If the range is already hidden it will unhide it.
Line (3) is just there, to ensure you explicitly select the require cell each time you want the code to run, so it automatically selects the cell next to the Target after the code has run.
In order to run the code you will have to place it a worksheet event by Right clickoing the sheet Tab, Select "View code" (Vb Window Appears) and pasting it into the Vb Window.

Please Login or Register  to view this content.

Hope you get it working
Regards Mick

Thats what I was looking for butcan you help me out how to use it in my sheets (Im kinda new learner :p )