Excel VBA - Help code scroll button to hide rows in multiple Sheets.

Sanchez88

New Member
Joined
Oct 16, 2017
Messages
7
-Excel 2013
-Windows 7 Professional

I would like my scroll button to hide the same rows in specific Worksheets.

I'm new to VBA so I used the Record Macro trick, however the constant switching the screen does causes the application to freeze. I wrote the code to the point where it hides the rows I need on a single page, can anyone tell me what I need to add to make it effect all pages without the page switching?

Any help would be greatly appreciated!

Code:
Private Sub SpinButton1_Change()

    If SpinButton1.Value = 30 Then
        
    Rows("10:38").Select
    Selection.EntireRow.Hidden = True
    
    End If


    If SpinButton1.Value = 29 Then
        
    Rows("10:10").Select
    Selection.EntireRow.Hidden = False
    Rows("11:38").Select
    Selection.EntireRow.Hidden = True
    
    End If
    
    If SpinButton1.Value = 28 Then
        
    Rows("11:11").Select
    Selection.EntireRow.Hidden = False
    Rows("12:38").Select
    Selection.EntireRow.Hidden = True
    
    End If
        
    If SpinButton1.Value = 27 Then
        
    Rows("12:12").Select
    Selection.EntireRow.Hidden = False
    Rows("13:38").Select
    Selection.EntireRow.Hidden = True
    
    End If
    
    If SpinButton1.Value = 26 Then
        
    Rows("13:13").Select
    Selection.EntireRow.Hidden = False
    Rows("14:38").Select
    Selection.EntireRow.Hidden = True
    
    End If
    
    If SpinButton1.Value = 25 Then
        
    Rows("14:14").Select
    Selection.EntireRow.Hidden = False
    Rows("15:38").Select
    Selection.EntireRow.Hidden = True
    
    End If
    
    If SpinButton1.Value = 24 Then
        
    Rows("15:15").Select
    Selection.EntireRow.Hidden = False
    Rows("16:38").Select
    Selection.EntireRow.Hidden = True
    
    End If
    
    If SpinButton1.Value = 23 Then
        
    Rows("16:16").Select
    Selection.EntireRow.Hidden = False
    Rows("17:38").Select
    Selection.EntireRow.Hidden = True
    
    End If


    If SpinButton1.Value = 22 Then
        
    Rows("17:17").Select
    Selection.EntireRow.Hidden = False
    Rows("18:38").Select
    Selection.EntireRow.Hidden = True
    
    End If
    
    If SpinButton1.Value = 21 Then
        
    Rows("18:18").Select
    Selection.EntireRow.Hidden = False
    Rows("19:38").Select
    Selection.EntireRow.Hidden = True
    
    End If
    
    If SpinButton1.Value = 20 Then
        
    Rows("19:19").Select
    Selection.EntireRow.Hidden = False
    Rows("20:38").Select
    Selection.EntireRow.Hidden = True
    
    End If
    
    If SpinButton1.Value = 19 Then
        
    Rows("20:20").Select
    Selection.EntireRow.Hidden = False
    Rows("21:38").Select
    Selection.EntireRow.Hidden = True
    
    End If
    
    If SpinButton1.Value = 18 Then
        
    Rows("21:21").Select
    Selection.EntireRow.Hidden = False
    Rows("22:38").Select
    Selection.EntireRow.Hidden = True
    
    End If
    
    If SpinButton1.Value = 17 Then
        
    Rows("22:22").Select
    Selection.EntireRow.Hidden = False
    Rows("23:38").Select
    Selection.EntireRow.Hidden = True
    
    End If
    
    If SpinButton1.Value = 16 Then
        
    Rows("23:23").Select
    Selection.EntireRow.Hidden = False
    Rows("24:38").Select
    Selection.EntireRow.Hidden = True
    
    End If
    
    If SpinButton1.Value = 15 Then
        
    Rows("24:24").Select
    Selection.EntireRow.Hidden = False
    Rows("25:38").Select
    Selection.EntireRow.Hidden = True
    
    End If
    
    If SpinButton1.Value = 14 Then
        
    Rows("25:25").Select
    Selection.EntireRow.Hidden = False
    Rows("26:38").Select
    Selection.EntireRow.Hidden = True
    
    End If
    
    If SpinButton1.Value = 13 Then
        
    Rows("26:26").Select
    Selection.EntireRow.Hidden = False
    Rows("27:38").Select
    Selection.EntireRow.Hidden = True
    
    End If
    
    If SpinButton1.Value = 12 Then
        
    Rows("27:27").Select
    Selection.EntireRow.Hidden = False
    Rows("28:38").Select
    Selection.EntireRow.Hidden = True
    
    End If
     
    If SpinButton1.Value = 11 Then
        
    Rows("28:28").Select
    Selection.EntireRow.Hidden = False
    Rows("29:38").Select
    Selection.EntireRow.Hidden = True
    
    End If
    
    If SpinButton1.Value = 10 Then
        
    Rows("29:29").Select
    Selection.EntireRow.Hidden = False
    Rows("30:38").Select
    Selection.EntireRow.Hidden = True
    
    End If
    
    If SpinButton1.Value = 9 Then
        
    Rows("30:30").Select
    Selection.EntireRow.Hidden = False
    Rows("31:38").Select
    Selection.EntireRow.Hidden = True
    
    End If
    
    If SpinButton1.Value = 8 Then
        
    Rows("31:31").Select
    Selection.EntireRow.Hidden = False
    Rows("32:38").Select
    Selection.EntireRow.Hidden = True
    
    End If
    
    If SpinButton1.Value = 7 Then
        
    Rows("32:32").Select
    Selection.EntireRow.Hidden = False
    Rows("33:38").Select
    Selection.EntireRow.Hidden = True
    
    End If
    
    If SpinButton1.Value = 6 Then
        
    Rows("33:33").Select
    Selection.EntireRow.Hidden = False
    Rows("34:38").Select
    Selection.EntireRow.Hidden = True
    
    End If
    
    If SpinButton1.Value = 5 Then
        
    Rows("34:34").Select
    Selection.EntireRow.Hidden = False
    Rows("35:38").Select
    Selection.EntireRow.Hidden = True
    
    End If
    
    If SpinButton1.Value = 4 Then
        
    Rows("35:35").Select
    Selection.EntireRow.Hidden = False
    Rows("36:38").Select
    Selection.EntireRow.Hidden = True
    
    End If
    
    If SpinButton1.Value = 3 Then
        
    Rows("36:36").Select
    Selection.EntireRow.Hidden = False
    Rows("37:38").Select
    Selection.EntireRow.Hidden = True
    
    End If
    
    If SpinButton1.Value = 2 Then
        
    Rows("37:37").Select
    Selection.EntireRow.Hidden = False
    Rows("38:38").Select
    Selection.EntireRow.Hidden = True
    
    End If
    
    If SpinButton1.Value = 1 Then
        
    Rows("38:38").Select
    Selection.EntireRow.Hidden = False
    
    End If
    
End Sub
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

tonyyy

Well-known Member
Joined
Jun 24, 2015
Messages
1,647
Sanchez88,

Welcome to the Board.

The Record Macro tool is quite handy but often doesn't write the most efficient code. For example, the use of Select/Selection is not only cumbersome but also slows execution significantly. You might consider changing...

Code:
Private Sub SpinButton1_Change()
Application.ScreenUpdating = False
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
    With ws
        If SpinButton1.Value = 30 Then
            .Rows("10:38").Hidden = True
            GoTo Nexxt
        End If
        
        If SpinButton1.Value = 29 Then
            .Rows("10:10").Hidden = False
            .Rows("11:38").Hidden = True
            GoTo Nexxt
        End If
        
        If SpinButton1.Value = 28 Then
            .Rows("11:11").Hidden = False
            .Rows("12:38").Hidden = True
            GoTo Nexxt
        End If
            
        If SpinButton1.Value = 27 Then
            .Rows("12:12").Hidden = False
            .Rows("13:38").Hidden = True
            GoTo Nexxt
        End If

        If SpinButton1.Value = 26 Then
            .Rows("13:13").Hidden = False
            .Rows("14:38").Hidden = True
        End If
    End With
Nexxt:
Next ws
Application.ScreenUpdating = True
End Sub
You'll need to edit/add the code for values 25 to 1.

Cheers,

tonyyy
 
Last edited:

tonyyy

Well-known Member
Joined
Jun 24, 2015
Messages
1,647
And if I understood your pattern correctly, you can replace all the IF statements with the code below...

Code:
Private Sub SpinButton1_Change()
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim i As Long
For i = 1 To 30
    If SpinButton1.Value = i Then
        For Each ws In ThisWorkbook.Worksheets
            If i <> 1 Then
                ws.Rows(38 - i + 1).Hidden = False
                ws.Rows(38 - i + 2 & ":38").Hidden = True
            Else
                ws.Rows("38").Hidden = False
            End If
        Next ws
        Exit For
    End If
Next i
Application.ScreenUpdating = True
End Sub
 

Sanchez88

New Member
Joined
Oct 16, 2017
Messages
7
Hi Tonyyy,

This is so much cleaner than my version and it works wonderfully, thank you!

Just a quick question, is there a way to specify which worksheets are affected?
I only need 3 out of the 5 total sheets to be changed, and I tried
Code:
 Set ws = Sheet("HVT Home Page", "Hourly Vote Tally Sheet" , "8 AM")
however it would not accept it. Any advice would be greatly appreciated, and thank you again with the massive help so far!
 

tonyyy

Well-known Member
Joined
Jun 24, 2015
Messages
1,647
Code:
Private Sub SpinButton1_Change()
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim i As Long
For i = 1 To 30
    If SpinButton1.Value = i Then
        For Each ws In ThisWorkbook.Worksheets
            [COLOR=#ff0000]If ws.Name = "HVT Home Page" Or ws.Name = "Hourly Vote Tally Sheet" Or ws.Name = "8 AM" Then[/COLOR]
'            If ws.Name <> "Sheet1" And ws.Name <> "Sheet2" Then
                If i <> 1 Then
                    ws.Rows(38 - i + 1).Hidden = False
                    ws.Rows(38 - i + 2 & ":38").Hidden = True
                Else
                    ws.Rows("38").Hidden = False
                End If
            [COLOR=#ff0000]End If[/COLOR]
        Next ws
        Exit For
    End If
Next i
Application.ScreenUpdating = True
End Sub
The line in red - "ws.Name =..." will include the named worksheets. (The line immediately below - commented out - will exclude the named worksheets. If you had a hundred sheets, it would be easier to exclude than exclude.) Use one or the other; not both.
 
Last edited:

Sanchez88

New Member
Joined
Oct 16, 2017
Messages
7
Oh this is perfect. You have no idea how much I was struggling with this, thank you so much for all your help!
 

Watch MrExcel Video

Forum statistics

Threads
1,099,008
Messages
5,465,995
Members
406,458
Latest member
Barboza Babcock

This Week's Hot Topics

Top