VBA: Hide columns in one sheet, given the result of a dropdown list in another sheet
Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

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

  1. #11
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,734
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

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

    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.

    Code:
    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





    Quote Originally Posted by mlikoudis View Post
    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
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  2. #12
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,734
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

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

    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.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  3. #13
    New Member
    Join Date
    Aug 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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:

    Code:
    If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
    Most examples I found online tend to start like this:

    Code:
    If Intersect(Target, Range("A9")) Is Nothing Then Exit Sub
    Thank you!

    Again thank you for you help!!!!!

  4. #14
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,734
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

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

    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.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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