Spin button for showing Month by Month on Chart Graph

Question

So i have the template layout in the picture attached below. I would like to know how to link the Spin button to able to show month by month when i click on the spin button. Here is the formula i have so far, everything working fine except for the chart doesn't connect to the spin.

E4 formula : ="Monthly "&E5&" - Audit Sheet Delivery"
data for eat month for LATE row:=IFERROR(VLOOKUP(G3,$A$4:$C$6000,2,FALSE)," ")
Same for On-Time:=IFERROR(VLOOKUP(G3,$A$4:$C$6000,3,FALSE)," ")
Spin button link to cell $E$5

I have created define name for each Month, Late and On-Time and linked it to the chart but it doesn't work. Please point out what i have to do in this case. Also, please show the code on here due to i am new to this. Thanks

Answer 1

How to do anything anything Excel can do, but with VBA code:

Virtually anything you can do manually in Excel (ie, via the ribbons/toolbars) has an equivalent in VBA.

If you are unsure of which VBA method or property to use, you can turn on the macro recorder and manually perform the action. The macro recorder translates your actions into Visual Basic code. After you record your actions, you can modify the code to do exactly what you want.

_{There are some limitations to recording macros, so if needed, certain actions have to be added after recording, such as conditional branches (eg., IF statements), variables usage loops, error handling, and text selections made with the mouse (however keyboard combinations do record).}

---

Record an action:

For example, if you want to automate the process of saving the file with a new name, but you don't know which property or method to use, you could do this:

1. On the Developer ribbon, click
   Change the default macro name to a name of your choice and click OK to start the recorder.
2. Hit F12 to open the Save As... dialog and save the file with a new name.
3. On the Developer ribbon, click
4. On the Developer ribbon, click Macros.
5. Select the macro name that you assigned (in Step 2) and click to view the VBA code within.

See the tips & resources at the links below to help get you started with automatizing Excel (and other Office applications) using VBA code.

---

More information:

• Office.com : Assign a macro to a Form or a Control button

• MSDN : Getting Started with VBA in Office

• MSDN : Recording a Macro to Generate VBA Code)

• MSDN : Revising Recorded VBA Macros

• homeandlearn.org : Excel VBA For Complete Beginners

• Stack Overflow : Overview of Form Controls and ActiveX Controls

---

_{Original Post:}

How to filter a chart

The ability to filter a chart by month is built-in to Excel. After you create a chart, you can change the data series in two ways:

• Use chart filters to show or hide data in your chart, or,

• Use the Select Data Source dialog box to edit the data in your series or rearrange them on your chart.

Filter data in your chart

1. Click anywhere in your chart.

2. Click the Chart Filters button next to the chart.

3. On the Values tab, check or uncheck the series or categories you want to show or hide.

4. Click Apply.

5. If you want to edit or rearrange the data in your series, click Select Data, and then follow steps 2-4 in the next section.

Once you know how to use the filters, you can record a macro while filtering as required, an use the code for buttons or other controls.

---

More Information:

• Office.com : Change the data series in a chart

• MSDN : Recording a Macro to Generate Code

• MSDN : Revising Recorded Visual Basic Macros

Answer 2

Thanks you all for your help. I did figure out one way to make it work for me. However, i will more than happy to learn new way or a better way to use spin button and scroll button to changing my chart.

1. Changes data layout table
2. Using index formula and vlookup (Cell F19, G19,H19) formula included in picture below)
3. Changes define name formula to pull data from Cell (Cell F19, G19,H19).
4. Now i can click on my spin button and the chart changing month by month for me.

It is working fine for me. But i love to learn more if anyone can show me a better way to do it. Please include pictures and formula in your answer. It will help me and other easy to follow it. Thanks

Answer 3

Steps

This is broadly similar to yours. Below is just meant to show you how to use dynamic named ranges as chart series sources. If you later decide to specify start and end ranges of months, you could use this same model and have the start and end indexes generate the height argument for Offset thus being able to plot ranges (though they would be aggregate).

Using the same data layout as in your answer.

---

① Add a forms control spin button, assign its linked cell as K3, then put the font to white so it is not visible. Its values run from 1-12 with increment 1.

---

② I then create two dynamic ranges:

1. LATE with formula =OFFSET(Sheet1!$G$4,Sheet1!$K$3-1,0,1,1)
2. OnTime with formula =OFFSET(Sheet1!$H$4,Sheet1!$K$3-1,0,1,1)

You can add these via name manager (Alt+F3)

Name manager:

---

③ I then insert a bar chart and add two series which use these dynamic named ranges as their source:

The above is for Late and this is repeated for OnTime. Note that the workbook name goes before the reference to the dynamic range.

---

④ In J2 I enter the chart title text "Monthly - 5 KPI Delivery" and point the chart title at this with = J2 in formula bar whilst chart title is selected on the chart.

---

⑤ K2 has the formula =INDEX(F4:F15,K3) and is used to retrieve the Month name from the list of months via Index with row argument the linked cell value from the spin button.

I then edit the chart X axis source with formula =Sheet1!$K$2 so the month name appears on the bottom of the chart.

---

⑥ Finally, any other chart sprucing you like. I chose to group and lock the chart and spin button together so they will move as a unit and to ensure data point values where shown.

---

Final result:

---

In action: