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

S

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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
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:
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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:
Upvote 0
Oh this is perfect. You have no idea how much I was struggling with this, thank you so much for all your help!
 
Upvote 0
You're very welcome. Glad it worked out...
 
Upvote 0
You must log in or register to reply here.

Similar threads

imback2nite
  • Solved
VBA help.
2
Replies
10
Views
349
Peter_SSs
Peter_SSs
B
  • Locked
  • Question
Multiply VBA Macro for hiding and unhiding rows
Replies
4
Views
376
RoryA
RoryA
E
  • Question
Help Cleaning Up / Optimizing VBA Code
Replies
6
Views
269
offthelip
O
S
  • Question
Excel VBA - Issue with Code running on drop down selection
Replies
9
Views
667
Candyman8019
C
B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
383
kevin9999
K

Forum statistics

Threads
1,214,575
Messages
6,120,334
Members
448,956
Latest member
Adamsxl

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top