Solved

Using named ranges to unhide columns

Posted on 2014-04-01
8
196 Views
Last Modified: 2014-04-02
I would like to have an activeX list box that hides or unhides one set of columns for each month.

I have 6 columns of data for each month, so I would like to have the named ranges  in the vba code for the list box.

Example is:
jan = columns f thru k;
feb = columns l through q
continuing in the same manner through named range: dec
0
Comment
Question by:Patmurf
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 39969232
your question isn't clear perhaps if you could attach a sample file would make it clear.
0
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39969593
Hi,

pls try

in my example I Only used Jan and Feb as Named Ranges and a validation list

Private Sub Worksheet_Change(ByVal Target As Range)
Dim arrMonths As Variant
arrMonths = Array("Jan", "Feb", "Mar", "Apr", "Mai", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

If Target.Address = "$A$1" Then
    For Each mth In arrMonths
        If mth = Target.Value Then
            Range(mth).EntireColumn.Hidden = False
        Else
            Range(mth).EntireColumn.Hidden = True
        End If
    Next
    
End If
End Sub

Open in new window

Regards
EE20140401.xlsm
0
 

Author Comment

by:Patmurf
ID: 39969785
I have attached my spreadsheet. You can see an activex button to hide sheets at about cells d3-d4.

My hope is to have an active x button that can hide or unhide individual columns of months but to do it by range names rather than cell references.

I have various lists on the sheet named "formula" that can be referenced in vba.

My problem is that although I am a sophisticated end user, I am a babe in the woods with vba - but I learn quickly and if an example is given I can extrapolate it to other uses.
incomeexp2014.xlsm
0
Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

 
LVL 22

Expert Comment

by:Ejgil Hedegaard
ID: 39970481
Replace
Columns("f:k").EntireColumn.Hidden = True
with
Range("jan").EntireColumn.Hidden = True

or to make a flip hide/unhide
    If Range("jan").EntireColumn.Hidden = True Then
        Range("jan").EntireColumn.Hidden = False
        Range("jan").Cells(1, 1).Select
    Else
        Range("jan").EntireColumn.Hidden = True
    End If
0
 

Author Comment

by:Patmurf
ID: 39971067
To Master:

Two Items:
First, thanks for your response, both examples are where I want to go.

1. When I do the simple replacement, I get the message:  run-time error 1004, Method 'range' of object'_worksheet failed.

2. I would love to use your flip hide/unhide except I don't know enough about how to start the beginning of the code. If you could give more help on both, I will try to get both to work.
0
 
LVL 22

Expert Comment

by:Ejgil Hedegaard
ID: 39972914
The error is typically because the range name can not be found, so check the spelling.
Range("jan") refers to a name on the active sheet.
The sheet Summary_detail is active, and you already have the named range "jan"=Summary_detail!$F:$K, so it should work.

For Case "jan" in the sub listbox2_Click() in the sheet module for Summary_detail, I replaced the line Columns("f:k").EntireColumn.Hidden = True, with Range("jan").EntireColumn.Hidden = True and it worked fine.

The flip code replace the line, and will work better if used with a double click = put into the sub listbox2_DblClick, because single click only fires when selection change.
Then hide/unhide will work even if jan is already active.
The line Range("jan").Cells(1, 1).Select selects the upper left corner of the range.
0
 

Author Comment

by:Patmurf
ID: 39973969
WOW, IT WORKS!! No surprise to you, I'm sure but I think it's GREAT

Thanks a million for hanging in with this programming neophyte.
0
 

Author Closing Comment

by:Patmurf
ID: 39973973
I appreciate your hanging in with me. I'm sure there are times when you grit your teeth at the ignorance of the person asking the question.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question