Solved

Using named ranges to unhide columns

Posted on 2014-04-01
8
194 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 49

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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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
 

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

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

810 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