Excel: How to make spinbutton hide or unhide rows? 2

[rlee16]

I have data that can be viewed as summary as well as continue to expand into more and more detail.

As such, I would like to place a spinbutton on the worksheet that allows me to hide or unhide rows. I don't really know how a spinbutton works and the VBA help example is too high level for me at this stage to extrapolate its principles. Thanks in advance for your help.

 

[chandlm]

Hi rlee16,

might I suggest you think of using a toggle button instead

Try adding the button to your sheet then add the following code to the togglebutton_click event

Private Sub ToggleButton1_Click()
Select Case ToggleButton1.Value

Case True
Columns("I:L&quot.EntireColumn.Hidden = True 'this will hide the specified columns and change the button caption
ToggleButton1.Caption = "Unhide columns"

Case False
Columns("I:L&quot.EntireColumn.Hidden = False 'this will unhide the specified columns and change the button caption

ToggleButton1.Caption = "Hide columns"
End Select

End Sub

Of course don't forget to change the code for the actual columns you want to hide.

HTH

Matt

 

[rlee16]

Matt,

Thanks for the help, but I'm looking for some guidance on a spinbutton. From my understanding a toggle button only has two choices -- I need something that can express many levels. For example, the top level would be Total Expenses, hidden below this summary line is a breakdown of total expenses, and hidden below each of these lines is a further breakdown. By just pressing the spinbutton, I want to be able to unhide these sections as well as hide them again. It's ok if I have to do this in the same order.

In short, would love some help on the spinbutton.

Richard

 

[chandlm]

Sorry Richard,

I just assumed that you wanted to hide a block of rows at a time.

Well onwards to the spinbutton.....

Add your spin button to the sheet and double click to access the code window.


In the properties window you will need to set the min property to 2. this will stop you hiding the first row.

Next set the max property to the last row you may need to hide.


Now in the change event for the spin button add the following code



Rows("1:" & SpinButton1.Max).Hidden = False ' Clears the previous hidden selection ready for the new one.

If SpinButton1.Value = SpinButton1.Max Then ' this checks if the spinbutton is the last row or not. if it is it will exit the macro keeping all of the lines unhidden
Exit Sub
Else
Rows(SpinButton1.Value & ":" & SpinButton1.Max).Hidden = True
End If


Have fun trying this out

HTH

Matt

 

[bryanbayfield]

Sounds like you could avoid using code altogether.

Have a look at the Excel (_not_ VBA) help files on Subtotals and Grouping.

 

[rlee16]

Matt,

This is exactly what I was looking for! Thanks! Star for you.

Richard

oops, I inadvertently gave bryan a star too.

 

[bryanbayfield]

I'm puzzled - why recreate Excel functionality using VBA - isn't it a waste of time and effort when it's already there for you?

From the help files:

About outlining a worksheet
How outlines work
When data is in list form, Microsoft Excel can create an outline to let you hide or show levels of detail with a single mouse click. An outline lets you quickly display only the rows or columns that provide summaries or headings for sections of your worksheet, or display the areas of detail data adjacent to a summary row or column.

Displaying and hiding detail data An outline can have up to eight levels of detail, with each inner level providing details for the preceding outer level. In the following example, the row containing the grand total of all the rows is level 1, the rows containing totals for the South and West regions are level 2, and the detail rows for the regions are level 3. To display only the rows for a particular level, you can click the number for the level you want to see. The detail rows for the West region are hidden, but you can click the + outline symbols to display the detail rows.



Ways to outline data
Inserting automatic subtotals also creates an outline If you use the Subtotal command (Data menu) to add subtotals to a list organized in rows, Excel outlines the worksheet so that you can show or hide as much detail as you need.

Outlining a worksheet automatically If you have summarized data by using formulas that contain functions, such as SUM, Excel can automatically outline the data, as in the preceding example. The summary data must be adjacent to the detail data.

Outlining a worksheet manually If the data is not organized so that Excel can outline it automatically, you can create an outline manually. For example, you'll need to manually outline data if the rows or columns of summary data contain values instead of formulas, such as in the example below. If you want to hide the detail rows for April and May, you can do so by outlining the list manually.



Because the totals in this list were typed as numbers, not calculated as formulas, automatic outlining won't work.

The pictures in the help file obviously aren't visible, but it shouldn't matter.

 

[rlee16]

Bryan,

I am very familiar with outlines and there are major limitations in terms of controlling presentation. On an aesthetic level, the outlines levels on the left side of the screen takes up valuable screen real estate. Second, the sum functions are not practical unless it is pure data (i work primarily with financial models).

On the benefits of VBA, I can hide and unhide different sections with one simple click, whereas with the outline I would have to individual click on each section I wanted to see or not see.

in general, i do not see using VBA as some alternative to using excel's built in tools, but rather a way to harness them in a way more powerful than simply using what's already there.

 

[bryanbayfield]

Fair enough.

I didn't want you to rely on VBA when there was already functionality in Excel, but if that doesn't suit your needs then obviously VBA is the way to go.

Glad you got it sorted.