Unhiding Columns

[howard]

I use a spreadsheet that the contains the months of the year as headings. I hide all the column headings and each month only unhide the current months heading for Eg Sept 2006

I need to write VBA code that will unhide the column for eg Sept 2006

Your assistance will be most appreciated

Howard

 

[dave3009]

You could use Select case for that purpose. This could be linked to a control or set to do it automatically.


HTH


Dave

 

[Peter_SSs]

howard

Can you tell us:
1. Which columns are the month headings in? and which row?
2. Exactly what the headings say (at least a couple of them)?
3. Whether those headings are actual text or dates entered in the heading cells but formatted as text?

 

[howard]

Hi Peter

The dates are in columns B:M in row 4

The dates are formatted as mmmm-yyyyy

I have copied the dates from my spreadsheet below

October-2005 November-2005 December-2005
January-2006 February-2006 March-2006
April-2006 May-2006 June-2006
July-2006 August-2006 September-2006


Howard

 

[dave3009]

Hi Howard

If this is pasted into ThisWorkbook module you column will update automatically on opening your workbook. You can change this as you feel necessary to suit your needs, i.e. linking to control etc. You will need to alter the column letters and sheet name and complete the private constants cases for each month

Option Explicit
 
Private Const Jan As Integer = 1
Private Const Feb As Integer = 2
Private Const Mar As Integer = 3
' Complete for every Month


Private Sub Workbook_Open()
    Dim iMonth As Integer
    Dim MyColumn As Range

    iMonth = DateTime.Month(DateTime.Now())
    
    ' The above will set it automatically
    
With Sheets("Sheet1")
Select Case iMonth
Case Jan:
        Set MyColumn = .Range("B:B")
        'Changing column name to suit your needs
Case Feb:
        Set MyColumn = .Range("C:C")
    'etc etc etc for each month
        End Select
        
.Range("B:M").Select
Selection.EntireColumn.Hidden = True
    
        MyColumn.Select
        Selection.EntireColumn.Hidden = False
End With
End Sub

HTH


Dave

 

[howard]

Hi Dave

Thanks for your help

Howard

 

[Peter_SSs]

howard

If the dates in row 4 are actually 1 October 2005, 1 November 2005 etc, then try this Worksheet_Activate code

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Activate()
    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    Columns("B:M").Hidden = <SPAN style="color:#00007F">True</SPAN>
    <SPAN style="color:#00007F">For</SPAN> c = 2 <SPAN style="color:#00007F">To</SPAN> 13
        <SPAN style="color:#00007F">If</SPAN> Cells(4, c).Value = DateValue(Now) - Day(Now) + 1 <SPAN style="color:#00007F">Then</SPAN>
            Cells(4, c).EntireColumn.Hidden = <SPAN style="color:#00007F">False</SPAN>
            <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> c
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>