VBA: Hide columns in one sheet, given the result of a dropdown list in another sheet

[mlikoudis]

Hey guys!

I'm a newbie on VBA and I need some help with a VBA code. Baiscally I have a workbook with several sheets. In one sheet (Summary), I have a dropdown list with all the months in the year (dropdown list is in cell "F9". What I would like to do is that: if Januray is select in the "Summary" sheet, then to hide specific columns of my choosing in the "Deposits" sheet.

I have found online plenty of VBA codes to hide colums based on dropdown list, but all the info is on one sheet (both the dropdown list and the columns to hide are on the same sheet). How do I go about making the VBA so that if there is a change in the "Summary" sheet, to perfom the actions in the "Deposits" sheet?

Thank you for your help

 

[My Aswer Is This]

Then my script should work for you.

Just add the Months and Quarters needded and the columns to hide.

I did January the way you said in your last post.

Try this and modify to your needs you should be able to do the other ones.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  8/16/2019  4:51:26 PM  EDT
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
    If Target.Address = Range("F9").Address Then
    With Sheets("Deposits")
        Select Case Target.Value
            Case "January"
                .Range("E1  ,H1:BS1").EntireColumn.Hidden = True
            Case "February"
                .Range("B1,C1, G1").EntireColumn.Hidden = True
            'Add more  here
            End Select
    End With
End If
End Sub

Allow Me to rephrase what I need by actually giving specifications:

I have to 2 sheets in a workbook. One sheet is named "Summary" and the other sheet is named "Deposits"

The sheet "Summary" has a dropdown list in cell F9 with the following selections: January, February, March, Q1, April, May, June, Q2, July, August, September, Q3, October, November, December, Q4.
The sheet "Deposits" contains columns with data relating to its respective month, as well as columns for the names of the accounts.

What I would like to do is automate the following:

If "January" is selected from the drop down menu (from the "Summary" sheet), then to hide the columns E and H:BS (from the "Deposits" sheet)
If "February" is selected from the drop down menu (from the "Summary" sheet), then to hide the columns E, G and I:BS (from the "Deposits" sheet)
If "March" is selected from the drop down menu (from the "Summary" sheet), then to hide the columns E, G:H and J:BS (from the "Deposits" sheet)
If "Q1" is selected from the drop down menu (from the "Summary" sheet), then to hide the columns E, G:I and K:BS (from the "Deposits" sheet)
And so on until Q4

Basically I want to hide the columns relating to the other months

I'm sorry if I didn't explain it clearly earlier. I was trying to keep my description in general terms so I can simply use the structure/concept of the code to my needs

 

[My Aswer Is This]

I test all my script and it worked for me.
The cell value must be exact.
And the script always looks in Range("F9") on sheet named Summary.

So it must be January not Jan

Or change the script.

 

[mlikoudis]

Hi My Answer Is This... I'm not sure what I was doing wrong last Friday, but I tried it today and it works perfectly! Thank you for you help!

If you have time, can you clarify one thing for me. Is there a reason as to why you start the code this way:

[COLOR=#333333]If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub[/COLOR]

Most examples I found online tend to start like this:

[COLOR=#333333]If Intersect(Target, Range("A9")) Is Nothing Then Exit Sub[/COLOR]

Thank you!

Again thank you for you help!!!!!

 

[My Aswer Is This]

A lot of coding can be done a lot of different ways.

The line of code in questions will not allow the code to run if you change more then one cell at a time.

So if you select a Range larger then one cell the script will not run.

Glad you have it working.