Solved

Using named ranges to unhide columns

Posted on 2014-04-01
8
195 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 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
Portable, direct connect server access

The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

 
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

Portable, direct connect server access

The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

790 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