creating a button to hide and unhide rows one by one in excel 2007

[gill123]

I would like to create a button (something like spin button) that can hide and un-hide row. I have rows from 1 to 200 with Age and i want the user to be able to see any row in between. by going up or down, they should be able to pick up any rows they want to look at. Is this possible without doing a VBA code. I yes, how?

Thank you

[xladept]

You could just Freeze Panes after your header rows

[gill123]

I have a graph reading the data, when i just do Freeze panes it does not change the graph. when i go and hide the row, my graph changes and show we ages for only the data that is unhidden. So i prefer to have a button that will do the hide and unhide, instead of right clicking on the row and hiding the row.

[xladept]

I think that it requires VBA

[gill123]

Would you be able to tell me how I can input VBA code and what is the code?

[mikerickson]

"Is it possible?" Yes, but its not clear to me which row you want un/hidden based on the spin button being pressed.

You have rows 1-200. If they are all visible, which rows do you want hidden if the down is pressed? etc.

Also, have you looked at AutoFilter?

[gill123]

I would like my users to just see the form that i created not to touch the data, That is why I don't want to use Auto Filter. Also, I have data in row starting at 65 to 265. I would like the spin button to go threw each row to hide and unhide. There are graphs linked to this data as well. Users will see the graphs change when they hide or unhide the rows. For example, if the users want to see the data in row 70 threw 100. They should be able to hide rows before 70 and after

[mikerickson]

"if the users want to see the data in row 70 threw 100. They should be able to hide rows before 70 and after"

How does the Excel know that the user wants 70:100?

Does the spin button change the 75 or the 100 or is there some other mechanism for the user choosing the rows to see?

[gill123]

age data.xlsx.pngEach row has a age in years as data, if they only want to look at failures that occur before they turned 70 years old they would need to hide the other rows. I have attached the screen shot of what I am talking about. I don't want user to touch the data under the graphs, There is form created on top of the graph, where i would like to place the spin button. Is it possible or no?

[mikerickson]

Its possible, maybe.

Two spin buttons would be the way that I went.
One would change the first invisible row.
the other would change the last visible row.

I'll revisit this thread after I get off work.

[mikerickson]

This code will work with two spin buttons from the Forms menu.
Assign the macro TopChange to both of them.
Adjust HideableRange to the range to match your situation
Run SetUp to set the initial variables.


This is a bit clunky, since the Form menu buttons don't fire until the mouse button is raised.
My Mac doesn't use ActiveX controls, but two ActiveX spin buttons and their Change event would be a smoother user interface.

Please Login or Register  to view this content.

[LordLoki]

If i see it correctly the users reference to what he want to see would be the horizontal Axis with 0-99.
So if i am the user i should be able to type 5 - 20 and it would hide all rows that have ageyears smaller 5 and bigger 20 so that they dissappear in the Graph
Is that correct so far?

[LordLoki]

coputer posted 2 times :D

[gill123]

Lord Loki , yes you are correct
Mike I am working on putting the code in, thank you

[LordLoki]

hi Gill,

I made an Example where i use a userform with 2 comboboxes to set the Range.
You could modify the code to use worksheet change so the user can type the values in a cell next to the Chart
hope that helps you

Greets
Loki