Worksheet Change: Display/hide columns depending on date

[dd]

I currently use a userform to display specific columns of data. I would like
to do this automatically depending on the present date.

I figure I may be able to use the worksheet change event to automatically
display and hide specific columns depending on the date. I would also like
to retain the userform to change between them if needed.

My change criteria is as follows:

If the date is less than the fourth of the month, but greater than 25th of
the previous month I want to hide columns J:U using something such as...
(from my current userform)...

Application.Calculation = xlManual
ActiveSheet.Unprotect
Cells.Select
Range("A:Z").Activate
Selection.EntireColumn.Hidden = False
ActiveWindow.SmallScroll ToRight:=4
Columns("J:U").Select
Selection.EntireColumn.Hidden = True
ActiveWindow.ScrollColumn = 1
Range("B10").Select
Application.Calculation = xlCalculationAutomatic

If the date is less that the 11, but greater or equal to the fourth of the
month...

Application.Calculation = xlCalculationManual
ActiveSheet.Unprotect
Cells.Select
Range("A:Z").Activate
Selection.EntireColumn.Hidden = False
Columns("G:I").Select
Selection.EntireColumn.Hidden = True
Columns("M:U").Select
Selection.EntireColumn.Hidden = True
ActiveWindow.ScrollColumn = 1
Range("B10").Select
Application.Calculation = xlCalculationAutomatic

If the date is less than 18th, but greater or equal to the 11th...

Application.Calculation = xlCalculationManual

ActiveSheet.Unprotect
Cells.Select
Range("A:Z").Activate
Selection.EntireColumn.Hidden = False
Columns("G:L").Select
Selection.EntireColumn.Hidden = True
Columns("P:U").Select
Selection.EntireColumn.Hidden = True
ActiveWindow.ScrollColumn = 1
Range("B10").Select
Application.Calculation = xlCalculationAutomatic

And finally, if the date is less than 25th, but greater or equal to the 18th

Application.Calculation = xlCalculationManual
ActiveSheet.Unprotect
Cells.Select
Range("A:Z").Activate
Selection.EntireColumn.Hidden = False
Columns("G:R").Select
Selection.EntireColumn.Hidden = True
ActiveWindow.ScrollColumn = 1
Range("B10").Select
UserForm1.Hide
Application.Calculation = xlCalculationAutomatic

Kind Regards
Dylan Dawson

 

[Joel]

I think it is better to be in a workbook open fuinction than a worksheet
change. I cleaaned up the code by using a select case statement. Try this

Private Sub Workbook_Open()

Application.Calculation = xlManual
ActiveSheet.Unprotect

Columns("G:I").Select
Selection.EntireColumn.Hidden = True

Select Case Day(Now())

Case 1 To 3, 26 To 31
Columns("A:Z").Hidden = False
ActiveWindow.SmallScroll ToRight:=4
Columns("J:U").EntireColumn.Hidden = True

Case 4 To 10
Columns("G:I").Hidden = True
Columns("M:U").Hidden = True

Case 11 To 17

Columns("G:L").Hidden = True
Columns("P:U").Hidden = True

Case 18 To 25
Columns("G:R").Hidden = True
End Select

ActiveWindow.ScrollColumn = 1
Range("B10").Select
Application.Calculation = xlCalculationAutomatic

End Sub

 

[Joel]

I made a small mistake with copying your code. Here is the corrected version.

Private Sub Workbook_Open()

Application.Calculation = xlManual
ActiveSheet.Unprotect

Columns("A:Z").Hidden = False

Select Case Day(Now())

Case 1 To 3, 26 To 31

ActiveWindow.SmallScroll ToRight:=4
Columns("J:U").EntireColumn.Hidden = True

Case 4 To 10
Columns("G:I").Hidden = True
Columns("M:U").Hidden = True

Case 11 To 17

Columns("G:L").Hidden = True
Columns("P:U").Hidden = True
Case 18 To 25
Columns("G:R").Hidden = True
End Select

ActiveWindow.ScrollColumn = 1
Range("B10").Select
Application.Calculation = xlCalculationAutomatic

End Sub

 

[dd]

Joel,

Thank you very much for providing a solution to my problem.

Kind Regards
Dylan Dawson


I made a small mistake with copying your code. Here is the corrected
version.

Private Sub Workbook_Open()

Application.Calculation = xlManual
ActiveSheet.Unprotect

Columns("A:Z").Hidden = False

Select Case Day(Now())

Case 1 To 3, 26 To 31

ActiveWindow.SmallScroll ToRight:=4
Columns("J:U").EntireColumn.Hidden = True

Case 4 To 10
Columns("G:I").Hidden = True
Columns("M:U").Hidden = True

Case 11 To 17

Columns("G:L").Hidden = True
Columns("P:U").Hidden = True
Case 18 To 25
Columns("G:R").Hidden = True
End Select

ActiveWindow.ScrollColumn = 1
Range("B10").Select
Application.Calculation = xlCalculationAutomatic

End Sub

 

[Joel]

If yhou put the code in a workbook open function then you shouldn't use
activesheet. You need to pick a sheet name.