Solved

Using named ranges to unhide columns

Posted on 2014-04-01
8
197 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 51

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
Technology Partners: 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!

 
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

Technology Partners: 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

Suggested Solutions

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 will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

752 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