Hi,
I recently created an excel spreadsheet that will help me track construction program on site. The spreadsheet is called a 3-week look ahead and the purpose of it is to have a complete overall construction program but based on use input, it only shows up to three weeks worth of construction timeline. For example, this construction timeline goes from the 13/03 to the 11/06.
The way I want this spreadsheet to function is when a user pick a weekending date in "C3", it shows the corresponding date i.e 13/03, and includes the following days for the next three weeks and hides the rest. If no dates are chose, i.e. "C3" is blank, it will show the overall program timeline as mentioned above. To this point, I have only manage to auto-hide everything but the chosen dates. This is based on me using an online tutorial. My challenge is to get it to include up to 3 weeks worth or program timeline based on the user selection, in which I have failed miserably. I am currently using Excel 2013 for this work. Alternatively, would appreciate if anyone here could point me to a similar thread. Thank you.
VBA code used are as follow:
Sheet1
Module
I recently created an excel spreadsheet that will help me track construction program on site. The spreadsheet is called a 3-week look ahead and the purpose of it is to have a complete overall construction program but based on use input, it only shows up to three weeks worth of construction timeline. For example, this construction timeline goes from the 13/03 to the 11/06.
The way I want this spreadsheet to function is when a user pick a weekending date in "C3", it shows the corresponding date i.e 13/03, and includes the following days for the next three weeks and hides the rest. If no dates are chose, i.e. "C3" is blank, it will show the overall program timeline as mentioned above. To this point, I have only manage to auto-hide everything but the chosen dates. This is based on me using an online tutorial. My challenge is to get it to include up to 3 weeks worth or program timeline based on the user selection, in which I have failed miserably. I am currently using Excel 2013 for this work. Alternatively, would appreciate if anyone here could point me to a similar thread. Thank you.
VBA code used are as follow:
Sheet1
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$3" Then
Dim the_selection As String
Dim week_in_review As String
the_selection = Sheet1.Range("C3")
Dim Rep As Integer
For Rep = 11 To 101
the_column = GetColumnLetter_ByInteger(Rep)
week_in_review = Sheet1.Range(the_column & "4")
Full_review = Sheet1.Range("J4")
If the_selection = week_in_review Then
Sheet1.Range(the_column & ":" & the_column).EntireColumn.Hidden = False
Else
Sheet1.Range(the_column & ":" & the_column).EntireColumn.Hidden = True
End If
If the_selection = Full_review Then
Sheet1.Range(the_column & ":" & the_column).EntireColumn.Hidden = False
End If
Next Rep
End If
End Sub
Module
Code:
Public Function GetColumnLetter_ByInteger(what_number As Integer) As String
GetColumnLetter_ByInteger = ""
MyColumn_Integer = what_number
If MyColumn_Integer <= 26 Then
column_letter = Chr(64 + MyColumn_Integer)
End If
If MyColumn_Integer > 26 Then
column_letter = Chr(Int((MyColumn_Integer - 1) / 26) + 64) & Chr((MyColumn_Integer - 1) Mod 26 + 65)
End If
GetColumnLetter_ByInteger = column_letter
End Function