PDA

View Full Version : [SOLVED] Hide and unhide columns according to number of days in my calendar



djemy1975
09-24-2019, 01:20 AM
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,

Paul_Hossler
09-24-2019, 06:55 AM
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

paulked
09-24-2019, 06:57 AM
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

paulked
09-24-2019, 06:57 AM
Beaten by 2 mins!

djemy1975
09-24-2019, 07:38 AM
Thank you sir for your prompt and working code.It is working.

djemy1975
09-24-2019, 07:42 AM
Thank you Mr Paul_Hossler (http://www.vbaexpress.com/forum/member.php?9803-Paul_Hossler) for your prompt and working code.