G
GoBucks
I am currently working on a report that will track and project a consultant’s
% utilization throughout the year. It consists of a range of columns which
contain week end dates (Fridays) from 1/1/10 – 2/25/11 (Row 6, Columns N
through BV). It also has a range of Week Numbers from 1 - 9 (Row 5, Columns N
through BV). The Week Nums correspond the Week End values on row 6.
I have a cell which contains a pick list (L2) for a user to select a mm-yy
value (e.g. Apr-10) or "All". Based on this value, my Worksheet Event code
hides all of the columns in the N:BV range except the columns that contain
the 1st week end date of the chosen month + the next 12 columns. For example,
if Apr-10 is in cell L2, then columns N:Z (Jan – Mar) and columns AN:BV (Jul
– Feb) are hidden. There is also a conditions that if L2 = "All", that the
entire range of N:BV will be unhidden.
I would like to add a condition to the current Worksheet Event Code that if
L2 = "Hide Prev Wks", then the code will hide ALL of the columns in the N:BV
range except 13 columns based off what value is in cell L3 (Planning Week
Number). Planning Week Number is also a picklist with values from 1 to 53.
For example, if L2 = "Hide Prev Wks" and L3 = 18, then columns N:AD (Weeks 1
- 17) and columns AN:BV (Weeks 31-9) are hidden.
Below is my current Worksheet Event Code. If you would like to see my file
for more context, I would be happy to send over as well. Thank you in advance
for your help.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Address = "$L$2" Then
If Target = "All" Then
Range("N:BV").EntireColumn.Hidden = False
Else
lastcol = Cells(6, Columns.Count).End(xlToLeft).Column
mr = Range("L2")
ff = mr - Weekday(mr - 6) + 7
fc = Application.Match(CLng(ff), Rows(6), 0)
lf = DateSerial(Year(mr), Month(mr) + 3, 1) -
Weekday(DateSerial(Year(mr), Month(mr) + 3, 2))
lc = Application.Match(CLng(lf), Rows(6))
Columns(14).Resize(, lastcol).Hidden = True
Range(Cells(6, fc), Cells(6, lc)).EntireColumn.Hidden = False
End If
Else
If Not Application.Intersect(Target, Range("N7:BV206")) Is Nothing
Then
On Error Resume Next
Application.EnableEvents = False
Range("M" & Target.Row,
Target).SpecialCells(xlCellTypeBlanks).Value = Target.Value
Application.EnableEvents = True
On Error Goto 0
End If
End If
End Sub
% utilization throughout the year. It consists of a range of columns which
contain week end dates (Fridays) from 1/1/10 – 2/25/11 (Row 6, Columns N
through BV). It also has a range of Week Numbers from 1 - 9 (Row 5, Columns N
through BV). The Week Nums correspond the Week End values on row 6.
I have a cell which contains a pick list (L2) for a user to select a mm-yy
value (e.g. Apr-10) or "All". Based on this value, my Worksheet Event code
hides all of the columns in the N:BV range except the columns that contain
the 1st week end date of the chosen month + the next 12 columns. For example,
if Apr-10 is in cell L2, then columns N:Z (Jan – Mar) and columns AN:BV (Jul
– Feb) are hidden. There is also a conditions that if L2 = "All", that the
entire range of N:BV will be unhidden.
I would like to add a condition to the current Worksheet Event Code that if
L2 = "Hide Prev Wks", then the code will hide ALL of the columns in the N:BV
range except 13 columns based off what value is in cell L3 (Planning Week
Number). Planning Week Number is also a picklist with values from 1 to 53.
For example, if L2 = "Hide Prev Wks" and L3 = 18, then columns N:AD (Weeks 1
- 17) and columns AN:BV (Weeks 31-9) are hidden.
Below is my current Worksheet Event Code. If you would like to see my file
for more context, I would be happy to send over as well. Thank you in advance
for your help.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Address = "$L$2" Then
If Target = "All" Then
Range("N:BV").EntireColumn.Hidden = False
Else
lastcol = Cells(6, Columns.Count).End(xlToLeft).Column
mr = Range("L2")
ff = mr - Weekday(mr - 6) + 7
fc = Application.Match(CLng(ff), Rows(6), 0)
lf = DateSerial(Year(mr), Month(mr) + 3, 1) -
Weekday(DateSerial(Year(mr), Month(mr) + 3, 2))
lc = Application.Match(CLng(lf), Rows(6))
Columns(14).Resize(, lastcol).Hidden = True
Range(Cells(6, fc), Cells(6, lc)).EntireColumn.Hidden = False
End If
Else
If Not Application.Intersect(Target, Range("N7:BV206")) Is Nothing
Then
On Error Resume Next
Application.EnableEvents = False
Range("M" & Target.Row,
Target).SpecialCells(xlCellTypeBlanks).Value = Target.Value
Application.EnableEvents = True
On Error Goto 0
End If
End If
End Sub