Solved

Using named ranges to unhide columns

Posted on 2014-04-01
8
184 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
8 Comments
 
LVL 25

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 48

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
 
LVL 21

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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 21

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Meetings to discuss business process can waste time, and often do .  The meeting's dialog can get confusing when participants have different professional perspectives and backgrounds.  A jointly-developed process picture helps wade through the confu…
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.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now