Consulting

Results 1 to 6 of 6

Thread: Hide and unhide columns according to number of days in my calendar

  1. #1

    Hide and unhide columns according to number of days in my calendar

    Hi friends and developpers,

    Please could you help me on this small program to follow-up trainees according to a specified calendar.

    What I want to do is :

    In "Trainees planning sheet ",I would like to hide and unhide columns (AH,AI,AJ) when I filter by month and date ,from dropdowns on the left, according to the number of days in each month and year.

    Is there any vba module that could do the trick?

    Please help as I am stuck in this problem since many days ago.

    Herewith a file you could work on:

    Best regards,
    Attached Files Attached Files

  2. #2
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,776
    Location
    Put this in a standard module and see

    You'll hjave to check that the date formats are the way you want, since I had to change some to test


    Option Explicit
    
    
    
    
    Sub Masquer_jour()
        Dim MonYr As Date
        Dim iMon As String, iYr As Long
        Dim ws As Worksheet
    
    
        Set ws = Worksheets("Trainees planning")
    
    
        iMon = ws.Range("B1").Value
        iYr = 2017 + ws.Range("B2").Value
        
        
        'make date and get last day of month (handles leap years)
        If iMon = 12 Then
            MonYr = DateSerial(iYr + 1, 1, 0)
        Else
            MonYr = DateSerial(iYr, iMon + 1, 0)
        End If
    
    
        ws.Range("G2").Value = DateSerial(Year(MonYr), Month(MonYr), 1)
        ws.Range("H2").Value = MonYr
    
    
        Application.ScreenUpdating = False
        ws.Columns("AH:AJ").Hidden = False
        Select Case Day(MonYr)
            Case 28
                ws.Columns("AH:AJ").Hidden = True
            Case 29
                ws.Columns("AI:AJ").Hidden = True
            Case 30
                ws.Columns("AJ:AJ").Hidden = True
        End Select
        Application.ScreenUpdating = True
    
    
    End Sub
    Attached Files Attached Files
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Expert paulked's Avatar
    Joined
    Apr 2006
    Posts
    509
    Location
    Option Explicit
    
    Sub Masquer_Jour()
        Dim rg As String
        rg = Left(Range("AH6"), 2)
        If rg = "01" Or rg = "02" Or rg = "03" Then
            Columns("AH").Hidden = True
        Else
            Columns("AH").Hidden = False
        End If
        rg = Left(Range("AI6"), 2)
        If rg = "01" Or rg = "02" Or rg = "03" Then
            Columns("AI").Hidden = True
        Else
            Columns("AI").Hidden = False
        End If
        rg = Left(Range("AJ6"), 2)
        If rg = "01" Or rg = "02" Or rg = "03" Then
            Columns("AJ").Hidden = True
        Else
            Columns("AJ").Hidden = False
        End If
    End Sub
    Paste the above into Module1
    Semper in excretia sumus; solum profundum variat.

  4. #4
    VBAX Expert paulked's Avatar
    Joined
    Apr 2006
    Posts
    509
    Location
    Beaten by 2 mins!
    Semper in excretia sumus; solum profundum variat.

  5. #5
    Thank you sir for your prompt and working code.It is working.

  6. #6
    Thank you Mr Paul_Hossler for your prompt and working code.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •