| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Change: Display/hide columns depending on date
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Worksheet Change: Display/hide columns depending on date
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 "dd" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Worksheet Change: Display/hide columns depending on date
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" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Worksheet Change: Display/hide columns depending on date
Joel,
Thank you very much for providing a solution to my problem. Kind Regards Dylan Dawson "Joel" wrote in message ... 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" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Worksheet Change: Display/hide columns depending on date
If yhou put the code in a workbook open function then you shouldn't use
activesheet. You need to pick a sheet name. "dd" wrote: Joel, Thank you very much for providing a solution to my problem. Kind Regards Dylan Dawson "Joel" wrote in message ... 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" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula to display text depending upon date entered in a cell | Excel Worksheet Functions | |||
| Change Background cell color depending on date and last value of c | Excel Programming | |||
| hide button depending on date | Excel Worksheet Functions | |||
| Hide or display column depending on cell content? | Excel Programming | |||
| Change the colour of a row depending on the contents of a date cell | Excel Programming | |||
Linear Mode
