Writing a Cycle Macro for Data Display

I'm looking for a Macro that cycles 3 columns of data so that only one is displayed at a time.  The data is range named to make it easier to refer to it.  I've mocked up a worksheet for you to take a look at.  For a great macro/VB programmer, this should be (hopefully) an easy macro.

Thank you in advance!!!

B.Cycle-Macro.xlsx
Bright01Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
Fairly simple, as you suggested:

Sub ShowColumn()
    Dim LoopNo As Integer
    Range("L:N").EntireColumn.Hidden = False
    LoopNo = Range("K1").Value

    If LoopNo = 1 Then
    Range("M:N").EntireColumn.Hidden = True
    Range("K1").Value = 2
    End If
    
    If LoopNo = 2 Then
    Range("L:L,N:N").EntireColumn.Hidden = True
    Range("K1").Value = 3
    End If
    
    If LoopNo = 3 Then
    Range("L:M").EntireColumn.Hidden = True
    Range("K1").Value = 1
    End If
    
End Sub

Open in new window

Start by entering number 1 in cell K1
0
Rob HensonFinance AnalystCommented:
You can achieve it without VBA as well, using a drop down selection and then one formula with 3 optional calculations in a column driven by the selection.
0
Bright01Author Commented:
Rob,

Greetings and thanks for the quick response!  The reason I used rangenames for the three sets of data was in order to keep from collapsing the entire column.  I have other data/formulas that are above the table I'm cycling through so if I collapse the entire column, I lose the view of the data above.  How do we cycle the 3 ranges?  I like having the helper cell for the identifier (K1).

B.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Rob HensonFinance AnalystCommented:
You can't just hide part of a column. Maybe the choice dependent calculation on the attached will suit better.

Use the dropdown in J2 to select 1 2 or 3 and the calcs in column P will change.
Cycle-Macro.xlsm
0
Rob HensonFinance AnalystCommented:
A combination of the two approaches would also be possible, ie just use the Cycle button to cycle through values 1 to 3 to impact the calculation in the column.

Updated version attached, use Cycle 2 button to impact column P
Cycle-Macro.xlsm
0
NorieAnalyst Assistant Commented:
Try this.
Sub CycleCalcs()
Static idx As Long
Dim arrHdrs As Variant
Dim arrCalcs As Variant
Dim arrFrmts As Variant

    arrHdrs = Array("rActual", "rPt.", "r%")
    arrCalcs = Array("=K7-I7", "=(K7-I7)*10", "=(K7-I7)/I7")
    arrFrmts = Array("0.0%", "0.00 ""Points""", "0.0%")

    With Range("L6")
        .Value = arrHdrs(idx)
        .Characters(1, 1).Font.Name = "Wingdings 3"
        .Characters(2).Font.Name = "Calibri"
        .Font.Underline = True
    End With
    
    With Range("L7:L13")
        .Formula = arrCalcs(idx)
        .NumberFormat = arrFrmts(idx)
    End With
    
    idx = (idx + 1) Mod 3
    
End Sub

Open in new window

2
Bright01Author Commented:
Rob,

Wow...... lots of choices here.  This is very good work but still doesn't give me what I'm looking for.  Very close however.  Particularly with the option you have provided that cycles through the single set of cells (i.e. does not collapse the columns).  I have two asks;

1.) Can we control the formatting so I can use the characters (e.g. wingdings3) for the titles?  See the notational  work from Norie above.
2.) Are you using the range names to call the cells?  That would make it easy for me to modify the ws without having to modify the cell references.

Thanks!!

Norie,

I loaded your code up, changed the reference macro to yours; but could not get it to fire.

B.
0
NorieAnalyst Assistant Commented:
Did you assign the macro CycleCalcs to the button?
Cycle-Macro-with-code.xlsm
0
Bright01Author Commented:
0
Bright01Author Commented:
Here's my rendition.
0
NorieAnalyst Assistant Commented:
Not sure what's happening, and I'm still investigating, but the code I posted should go in a standard module, not a sheet module.
0
Bright01Author Commented:
Norie,  I moved it to a module....... still doesn't fire.

B.
0
NorieAnalyst Assistant Commented:
Did you unhide column L?
0
Bright01Author Commented:
Wow..... now I really feel stupid!  Yes, it works great..... except, how do I use the range names instead of the cell references in the code?  I see you are pulling from an array.  I can use it as is if it's too hard to refer to the three range names.....

Thoughts?

Rob,

Hold your efforts, I think Norie has this almost nailed.


B.
0
NorieAnalyst Assistant Commented:
Well, in what I suggested the named ranges are kind of redundant because the code only actually refers/writes to one range.

If for some reason you wanted/needed a named range, e.g. the no of rows might expand, then the code could be changed to use a named range.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
Well you might be able to get the ASCII code for the delta symbol and incorporate it in the formula.
0
Bright01Author Commented:
Norie and Rob,  Thank you guys so much!  This is very usable in my model.   Norie, your code is very "elegant" and you were right as I reviewed it.... I don't need the range name(s).  Best Solution!  Rob, you were first on the scene and had some both creative and innovative ways to get this done.  I appreciate the efforts by both of you.

Have a great weekend!

B.
0
Bright01Author Commented:
Norie and Rob,  Thank you guys so much!  This is very usable in my model.   Norie, your code is very "elegant" and you were right as I reviewed it.... I don't need the range name(s).  Best Solution!  Rob, you were first on the scene and had some both creative and innovative ways to get this done.  I appreciate the efforts by both of you.

Have a great weekend!

B.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.