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?
 
NorieConnect With a Mentor VBA ExpertCommented:
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
 
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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
 
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 HensonConnect With a Mentor Finance 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
 
NorieVBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.