Button to hide or show rows

[jay-excel]

Hello,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I am trying to create a button that when clicked, will display a list with check boxes and ask which sections of a worksheet to hide or display.
<o> </o>
So, the button would have the ability to hide or show sections.
<o> </o>
Section 1 = rows 16-20
Section 2 = rows 21-27
Section 3 = rows 28-34
Section 4 = rows 35-42
Section 5 = rows 43-49
<o> </o>
<o> </o>
Is it also possible to reference these sections by their defined names? I have each of sections defined by a name as listed above (section 1, section 2 etc…)
<o> </o>
Thanks so much and let me know if you would like any additional information.
<o> </o>
Cheers
<o> </o>
- Jay

 

[Andrew Poulsom]

Sorry, but you can't have a name containing a space like Section 1 does.

 

[Misca]

Using the range names (no spaces allowed in the names) should work:

The macro to hide the rows for Section1 would be

Range("Section1").EntireRow.Hidden = True

Without the named range you can use cell references:

Range("A16:A20").EntireRow.Hidden=True

To change the hidden rows back to normal just change the True to False.

 

[Weaver]

What's the actual format of the names? Only asking cos I was under the impression range names couldn't contain spaces. I think it should be possible to achieve what you're after. Would the sections be displayed one at a time or would you need to be able to display multiple sections?

Are you able to design your own user form for this project?

 

[BC...]

Name your ranges, without the space, as Section1....etc. You could then add a button for each section that runs the script to show/hide that section.

Range("Section1").Select
Selection.EntireRow.Hidden = Not Selection.EntireRow.Hidden

You can take it a step further, this is good if you have a file with lots of buttons, by naming each button the similar to your named ranges, using the same #s. Then you can use the grab application.caller and evaluate it to determine what button was clicked and what action your script needs to take.

PIXCLICKED = Application.Caller
Application.StatusBar = CStr(Application.Caller)
Select Case Left(PIXCLICKED, 8)

 

[lancelotzip]

ahh that's handy thanks

 

[jay-excel]

Hello,

The

Range("Section1").Select
Selection.EntireRow.Hidden = Not Selection.EntireRow.Hidden


Works great! Thanks so much.

I am wondering if it is possible so, that when the button is clicked, a list appears where the the user is prompted to choose which lines are visible (hidden) or not.

so it would look like

Section1
Section2
Section3
Section4

and there would be check boxes next to each line to show/hide each area.

Thanks!

 

[Misca]

Why so many check boxes? It would be a lot easier (=less work) to use a column and have a macro hide all the rows where there's X on it.

Or you could put your range names in a list box / combo box and fire your macro from there - you don't even need the buttons to do that: Simple interface makes the workbook easier to use / maintain.