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
Bookmarks