| 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 |
| 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 | |||
Threaded Mode
