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

mlikoudis

New Member
Joined
Aug 14, 2019
Messages
7
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
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,952
Office Version
2010
Platform
Windows
Put this change_event code in the "Summary" sheet.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F9")) Is Nothing Then
    Application.EnableEvents = False
    With Sheets("Deposits")
        .Cells.EntireColumn.Hidden = False
        .Columns("A").Hidden = True  'Change column letter to suit
        'add other columns here
    End With
    Application.EnableEvents = True
End If
End Sub
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
You said:
then to hide specific columns of my choosing in the "Deposits" sheet.

How do you plan to this? Do you plan to choose the columns Manually before the script starts?

So if user selects March what column or columns will be hidden on Deposits sheet.
 

mlikoudis

New Member
Joined
Aug 14, 2019
Messages
7
The columns to hide are fixed. By my choosing, I meant that I will set the specific columns to hide depending on the user's month selection. For example: If January, then hide columns A, E, J. If February, B, C G...... And so on
 

mlikoudis

New Member
Joined
Aug 14, 2019
Messages
7
Hi JoeMo!

Thank you for the reply and your help! Correct if I'm wrong, but shouldn't the code include what the will happen depending on the value from the dropdown selection? The code setup you presented seems fine, but isn't it missing the value of F9? (example: if F9 = "January" Then hide columns A,B,D. If F9 = February, Then Hide colums C, E G. And so on......)

Put this change_event code in the "Summary" sheet.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F9")) Is Nothing Then
    Application.EnableEvents = False
    With Sheets("Deposits")
        .Cells.EntireColumn.Hidden = False
        .Columns("A").Hidden = True  'Change column letter to suit
        'add other columns here
    End With
    Application.EnableEvents = True
End If
End Sub
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
I could help you but still not sure I see a trend.


You said:
If January, then hide columns A, E, J. If February, B, C G...... And so on

I do not see a trend here.




 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,952
Office Version
2010
Platform
Windows
Hi JoeMo!Top

Thank you for the reply and your help! Correct if I'm wrong, but shouldn't the code include what the will happen depending on the value from the dropdown selection? The code setup you presented seems fine, but isn't it missing the value of F9? (example: if F9 = "January" Then hide columns A,B,D. If F9 = February, Then Hide colums C, E G. And so on......)
Sure, but you didn't provide much information on that so I provided a framework that obviously needs some additions, but only you know what combinations of months and columns should be in play.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
With the limited information you have provide.
Try this for January and February

You should be able to do the other Months.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window
Put this script in sheet named Summary.

When you change a value in Sheets Summary Range("F9") the script will run.

You will need to have a sheet named "Deposits"

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  8/16/2019  4:17: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("A1,E1, J1").EntireColumn.Hidden = True
            Case "February"
                .Range("B1,C1, G1").EntireColumn.Hidden = True
            'Add more  here
            End Select
    End With
End If
End Sub
 

mlikoudis

New Member
Joined
Aug 14, 2019
Messages
7
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
 

mlikoudis

New Member
Joined
Aug 14, 2019
Messages
7
I tried the code and it doesn't hide any columns... Perhaps I need to enable something?

Also, for my own personal education (so I can learn): Why start the code with the following: If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub

With the limited information you have provide.
Try this for January and February

You should be able to do the other Months.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window
Put this script in sheet named Summary.

When you change a value in Sheets Summary Range("F9") the script will run.

You will need to have a sheet named "Deposits"

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  8/16/2019  4:17: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("A1,E1, J1").EntireColumn.Hidden = True
            Case "February"
                .Range("B1,C1, G1").EntireColumn.Hidden = True
            'Add more  here
            End Select
    End With
End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,102,778
Messages
5,488,823
Members
407,658
Latest member
Arias610

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top