Hide columns automatically based on year and month imputs

[Legacy 464251]

Hello everyone! and thank you for your time.

I work as a financial analyst and I have a VBA question. I would appreciate if you can help me. I'm new to VBA.

So, what I want to do is for excel to hide automatically certain columns depending on the imputs on two cells, which indicate the YEAR and MONTH desired (cells P5 and P6 on the image below). I want the DAY to always be the last possible day of the month.

I have google this, and the best solution I have found is this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xCell As Range
    If Target.Address <> Range("O3").Address Then Exit Sub
    Application.ScreenUpdating = False
    For Each xCell In Range("B3:M3")
        xCell.EntireColumn.Hidden = (xCell.Value < Target.Value)
    Next
    Application.ScreenUpdating = True
End Sub

This code works great, but I have two issues. The first one is that it feeds the code with the date in the cell O3 rather than getting the year and month from P5 & P6. To solve this, I have used this formula on the cell O3 =DATE(P5;P6+1;0). The problem I am having now is that when I change either the year or month the colums wont hide until I click on O3 and press enter. I guess the code above requires you to interact with that cell specifically. Changing the value does trigger the code.

The second issue, a minor one, is that I want the month and year imput to be on a different sheet. As this cells are going to be linked to a bunch of sheets.


Can you guys please help me with this? I tried googling it but can't find a solution.

Thank you in advance.

 

[yky]

Would you post you data using XL2BB?

https://www.mrexcel.com/board/excel-articles/xl2bb-excel-range-to-bbcode.1/

 

[Dossfm0q]

Greetings

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xCell As Range
    If Intersect(Target, Range("O3,P5:P6")) Is Nothing Then Exit Sub
    
    Application.ScreenUpdating = False
    For Each xCell In Range("B3:M3")
    
        xCell.EntireColumn.Hidden = (xCell.Value < [O3].Value)
    Next
    Application.ScreenUpdating = True
End Sub

Second
just add SheetName with !

=DATE(Sheet2!P5;Sheet2!P6+1;0)

 

[Legacy 464251]

Hello Doss,

And thank you for the answer.

In the VBA code above, can the formula be edited so there is no need for the O3

 

[Legacy 464251]

Hello Doss,

And thank you for the answer.

I have tried the code and it works great.

I am wondering, in the VBA code above... can the code be edited so there is no need for the O3 cell to exist? The reason is because I dont want a date on the file because in the model I am working on we dont account for days.

Sorry for my ignorance, but I am not quite sure how to do the second change as wwll.

 

[Fluff]

Cross posted Hide columns automatically based on year and month imputs

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Legacy 464251]

Thanks. Issue solved!

 

[Dossfm0q]

Your Welcome it is Ok , and I am Sorry