• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA - automate shapes colour change and pie chart update

R

rick robin

New Member
i am trying to create a dynamic dashboard but have come stuck with automating excel shapes and pie chart to work in sync. i have tried various methods but to no avail/none work smoothly.

please can someone help.

what i am trying to do is:

(1) Based on an excel table with data - if the value in the cell is > 0 the upper arrow becomes visible or change from no fill to green - if the value is < 0 then down arrow change from no fill to red - if zero then remain no fill (invisible). i need this to update every time the cell changes.

(2) simultaneously depending on which values in the table changing the pie chart would update but hide the series that are not changing.

(3) an additional feature which i do nt know whether possible is if a slicer could be used so the user can select a particular series and just view that piece of the pie chart info instead of the full pie chart.

I have attached the WIP dashboard with the pie chart and up/down arrows i am working with.

Any help/advice would be appreciated.

Many thanks
 

Attachments

  • Book1.xlsx
    153.8 KB · Views: 18
A few issues

1. Arrows are easy, but what range / cells are they based on

2. Pie Chart is easy, but it is linked to an external Workbook and so what range / cells is it based on

3. Slicer, same as Q2 above

I'd suggest having the data on another worksheet even if it is hidden, rather than another file
 
hi Hui.

All the data related would sit in a separate hidden worksheet within the same workbook.

(1) based on range worksheet: Data A4:M4 and it's both up and down arrows based on the same cell in each instance it would use the variance between two numbers and if it's negative it activates the red arrow if positive value activate the green arrows

(2) the pie chart would be based on the same data but range worksheet: Data A2:M2 basically if the figure in row 2 changes I want the pie chart to only show the series where the value has shown an increase or decrease. those that are not changing will remain zero.

(3) slicer would feed from the same page using same table. clicking the slicer categories would change the pie chart effects.

thanks
 
see attached for 1 & 2
 

Attachments

  • Communications.xlsm
    165 KB · Views: 42
Hi @Hui,

the above works a treat how can I get the shapes to update when I put a formula in the data sheet range to change the values?
 
You must log in or register to reply here.
Back
Top