+ Reply to Thread
Results 1 to 4 of 4

Hide and Unhide sheets based on Date [better code]

Hybrid View

  1. #1
    Registered User
    Join Date
    10-05-2012
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    5

    Hide and Unhide sheets based on Date [better code]

    Hi, guys!

    today I want to ask for your help to optimize my code.
    I have UserForm where user select the type of update: Daily or Weekly (option buttons).
    There is a 7 sheets of weekdays (Mon, Tue, Wed ....) in that workbook and When update is done i need them hide and unhide based on:
    1st witch update is selected
    2nd on witch day of week the Daily update is made.

    I had wrote this Sub for this job, witch works great, but I'm looking at it and have this feeling - it's too big, is there a way to make it smaller/simpler?
    Ok, maybe it's simple enough but smaller would be great.
    Question is: is it possible?
    Thank you for reading all this.
    Here is the code:
    Public Sub HideUnhideSheets()
    
        Dim shMon As Worksheet
        Dim shTue As Worksheet
        Dim shWed As Worksheet
        Dim shThu As Worksheet
        Dim shFri As Worksheet
        Dim shSat As Worksheet
        Dim shSun As Worksheet
        
        With ThisWorkbook
            Set shMon = .Sheets("Mon")
            Set shTue = .Sheets("Tue")
            Set shWed = .Sheets("Wed")
            Set shThu = .Sheets("Thu")
            Set shFri = .Sheets("Fri")
            Set shSat = .Sheets("Sat")
            Set shSun = .Sheets("Sun")
        End With
            
            If UserFormUpdate4Daily.OptionButtonWeekly.Value = True Then
                    
                    shMon.Visible = True
                    shTue.Visible = False
                    shWed.Visible = False
                    shThu.Visible = False
                    shFri.Visible = False
                    shSat.Visible = False
                    shSun.Visible = False
                
            Else
                
                Select Case Weekday(Date, vbMonday)
                    Case 1
                        
                            shMon.Visible = True
                            shTue.Visible = True
                            shWed.Visible = True
                            shThu.Visible = True
                            shFri.Visible = True
                            shSat.Visible = True
                            shSun.Visible = True
                        
                    Case 2
                        
                            shMon.Visible = True
                            shTue.Visible = False
                            shWed.Visible = False
                            shThu.Visible = False
                            shFri.Visible = False
                            shSat.Visible = False
                            shSun.Visible = False
                        
                    Case 3
                        
                            shMon.Visible = True
                            shTue.Visible = True
                            shWed.Visible = False
                            shThu.Visible = False
                            shFri.Visible = False
                            shSat.Visible = False
                            shSun.Visible = False
                        
                    'case 4
                    'case 5
                
                End Select
                
            End If
            
        Set shMon = Nothing
        Set shTue = Nothing
        Set shWed = Nothing
        Set shThu = Nothing
        Set shFri = Nothing
        Set shSat = Nothing
        Set shSun = Nothing
        
    End Sub

  2. #2
    Valued Forum Contributor
    Join Date
    11-15-2008
    Location
    ph
    MS-Off Ver
    2007/2010/2016
    Posts
    479

    Re: Hide and Unhide sheets based on Date [better code]

    Hi -

    Hide them all initially;
    Public Sub HideUnhideSheets()
    
        Dim shMon As Worksheet
        Dim shTue As Worksheet
        Dim shWed As Worksheet
        Dim shThu As Worksheet
        Dim shFri As Worksheet
        Dim shSat As Worksheet
        Dim shSun As Worksheet
        
        With ThisWorkbook
            Set shMon = .Sheets("Mon")
            Set shTue = .Sheets("Tue")
            Set shWed = .Sheets("Wed")
            Set shThu = .Sheets("Thu")
            Set shFri = .Sheets("Fri")
            Set shSat = .Sheets("Sat")
            Set shSun = .Sheets("Sun")
        End With
                    shMon.Visible = False
                    shTue.Visible = False
                    shWed.Visible = False
                    shThu.Visible = False
                    shFri.Visible = False
                    shSat.Visible = False
                    shSun.Visible = False    
            If UserFormUpdate4Daily.OptionButtonWeekly.Value = True Then
                     shMon.Visible = True
            Else
                
                Select Case Weekday(Date, vbMonday)
                    Case 1
                        
                            shMon.Visible = True
                            shTue.Visible = True
                            shWed.Visible = True
                            shThu.Visible = True
                            shFri.Visible = True
                            shSat.Visible = True
                            shSun.Visible = True
                        
                    Case 2
                        
                            shMon.Visible = True
    
                        
                    Case 3
                        
                            shMon.Visible = True
                            shTue.Visible = True
    
                        
                    'case 4
                    'case 5
                
                End Select
                
            End If
            
        Set shMon = Nothing
        Set shTue = Nothing
        Set shWed = Nothing
        Set shThu = Nothing
        Set shFri = Nothing
        Set shSat = Nothing
        Set shSun = Nothing
        
    End Sub

  3. #3
    Registered User
    Join Date
    10-05-2012
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Hide and Unhide sheets based on Date [better code]

    I forgot to mention that these weekday sheets are in a middle of many other sheets. If it's important.
    I managed to think of how to make it smaller, at least a little bit. By removing empty rows in it [joke].

    My WB sheets system is something like this:
    1-5 sheets - charts, tables of data (always visible)
    6-12 sheets - weekdays charts (need to show/hide based on data available)
    13 - last sheet - data sheets (always hidden)

    So I thought that if I put this few lines in the begging of code:
    'Hides all sheets except 1st 5
        shNr = Sheets.Count
        Do Until shNr = 5
            ThisWorkbook.Sheets(shNr).Visible = False
            shNr = shNr - 1
        Loop
    I can get rid of all lines where I set sheet's visibility to FALSE later in code!

  4. #4
    Registered User
    Join Date
    10-05-2012
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Hide and Unhide sheets based on Date [better code]

    It looks like we posted at the same time, mate
    It's always nice when someone confirms your thoughts
    Thanks, event21!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1