VBA - Autohide a range of columns based on dropdown list options

[DimLM19]

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

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

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

 

[gallen]

Hi, and welcome.

Do you have an example of how your data is displayed on the sheet? Is there a column for each day of the week?

Also, there's a much simpler way to get a column letter from any row number, you don't need a separate function. You can delete the module and use:

the_column = Replace(Cells(1, Rep).Address(0, 0), "1", "")

It basically gets the address value of row 1 of the column you want and replaces the 1 in the address with a blank string.

 

[DimLM19]

Hi Gallen,

Thank you for responding to my issue. My spreadsheet is structured to look like the following:

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

2

Week ending From :

*User Drop down list*

3

Activity

Notice & Approval for Excavation Required

Notice & Approval for Excavation Granted

Inspect prior to concrete pour Hold Point

Inspect prior to Backfill Witness Point

PTW Required

PTW Provided

Percentage Complete

13/03

14/03

15/03

16/03

17/03

18/03

19/03

4

5

1

Preliminaries

54.8%

6

1.1

Site Set out positioning survey

100.0%

7

1.2

Site Ablutions Facilites provided

100.0%

8

1.3

Procurement of Reo & Formwork

10.0%

9

1.4

Procurement of Concrete

25.0%

10

1.5

Procurement of Conduits

30.0%

11

1.6

Scrape and Prepare Site

64.0%

<colgroup><col><col><col><col><col span="5"><col><col><col><col><col span="5"></colgroup><tbody>
</tbody>

Hope that explains the spreadsheet structure better and if you require any more information, please let me know. So basically from that, dates listed in the drop down list corresponds to dates listed in columns K, L, M, N up till column CW, i.e from 13/03 to 11/06 on a daily interval. When the user chooses a date from the drop down list, it will show dates (columns) up to 3 weeks. i.e. 21 days from the point that was chose and the rest will be auto-hidden. That would be the purpose of this spreadsheet.

 

[gallen]

Thanks. Try this. This assumes cell C3 is the dropdown list of all dates from K3 to CW3. It will display the date that is selected and the next 20 days after to show a total of 21 days. Should at least point you in the right direction.

Private Sub Worksheet_Change(ByVal Target As Range)    
    'K = 11
    'CW=101
    
    Dim i As Integer 'column counter
    
    If Target.Cells.Count > 1 Then Exit Sub
    
    If Target.Address = Range("C3").Address Then
        If Target = "" Then
            Range("K1:CW1").EntireColumn.Hidden = False
        Else
            For i = 11 To 101
                Cells(3, i).EntireColumn.Hidden = Cells(3, i) < Target Or Cells(3, i) >= Target + 21
            Next i
        End If
    End If
    
End Sub

 

[DimLM19]

Oh wow. Thank you so much. That worked like how I wanted to. Since I have just started to get involved in VBA, do you have any recommendations on learning sources? Thanks again.

 

[gallen]

Strange, I thought I'd replied to this. Sorry.

No, Google is your friend on this one. I learnt much from this forum, and from just learning everything I needed to do as and when it was required. Youtube has some good tutorials too. Check those out.