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
 

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!
 

Forum statistics

Threads
1,082,283
Messages
5,364,262
Members
400,787
Latest member
bs04c

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top