Solved

Dynamic Excel Countdown Graphic

Posted on 2016-11-30
21
35 Views
Last Modified: 2016-12-01
EE Pros,

I'm trying to show a Excel Graphic (single value Bar Chart) that is dynamic and represents a change over time.   With 5 Series, and a "START" and "END" value, I need a macro that counts from Start to End, places the "VALUE" in a cell that then is linked to the graphic that changes as the count takes place.  You can select Fast, Med. or Slow for the count speed.  

Take a look at the attached file and see if you are up for it.

Thank you in advance.

B.
Countdown-Graphic.xlsm
0
Comment
Question by:Bright01
  • 13
  • 8
21 Comments
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41907195
Lets see if I can do better here. Your graph is based on column "H" so changing column "F" won't directly change the graph unless there is a relationship between the two columns so should the formula for H12 be =F12? Also, please give me some idea of how fast ""Fast", "Med" and "Slow" are in seconds.
0
 

Author Comment

by:Bright01
ID: 41907204
Martin,

Thanks!  

The Start/End is not part of the Graphic.  The Value is the only plot point and is counted between the Start/End cells.  So, if F12 is "7" and G12 is "2", then the value in Cell H12 starts at 7.  When the Macro is fired, the count begins, (i.e. 7, 6,5,4,3,2) and stops at 2.  In Cell H12, this change is reflected as each number is placed in H12 (again, 7, 6, 5, 4, 3 and 2).  This gives the automation to the graphic.  Fast would be 1 sec. med. would be 3 sec. and slow would be 5 sec.  I'm assuming I can change that in the code.

Does that help?

B.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41907208
Yes it does.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41907257
Try this.
28986331.xlsm
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41907316
In this version I added code that changes the "START" shape's caption to tell you what's going on. Note that any time you change a value in range F12:G16 the cation will change back to "START".
28986331a.xlsm
0
 

Author Comment

by:Bright01
ID: 41907345
Martin,  You are "the Master"!  One tweek if you don't mind; can we add a "reset button so it starts over?

B.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41907361
Thanks. Is your comment based on the original version or the "a" version?

What would "Reset" do? Change H12:H16 back to what they where?

I also have another question. What do you want to have happen if Start = 1 and End = 8 and Value = 9 and the user clicks START? Currently the program would run until Value became a number large enough to cause an error because the logic says "add 1 to Value until Value = End  and that would never occur.
0
 

Author Comment

by:Bright01
ID: 41907509
I only looked at the second version (a?).  

A Reset sets the model up to run again just like it did originally.  So if the Start is 7 and the End is 2, it simply reruns the count (i.e. 7, 6, 5, 4, 3, 2 - stop).  

To answer the question of Start 1 End 8 and Value 9..... that should never happen.  If Start is 1 and End is 8, it simply counts up; (i.e. 1, 2, 3, 4, 5, 6, 7, 8 - stop) It never reaches 9 or anything above the End number....which in this case is 8.

Also, keep in mind, the reason for the table is that the numbers for Start and End can be adjusted.

Does that help?

B.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41907541
A Reset sets the model up to run again just like it did originally.  So if the Start is 7 and the End is 2, it simply reruns the count (i.e. 7, 6, 5, 4, 3, 2 - stop).  
I interpret this as being the same as "A Reset changes the values H12:H16 back to what they where before the START button was clicked". If it's something different then please explain.
To answer the question of Start 1 End 8 and Value 9..... that should never happen.  If Start is 1 and End is 8, it simply counts up; (i.e. 1, 2, 3, 4, 5, 6, 7, 8 - stop) It never reaches 9 or anything above the End number....which in this case is 8.

Also, keep in mind, the reason for the table is that the numbers for Start and End can be adjusted.
I understand that the Start and End can be adjusted and that's what worries me, so let me ask my question a different way. If H16 is 9 and the user changes F16 to, say, 2 and leaves G16 at 8 and then clicks START, what should happen?
0
 

Author Comment

by:Bright01
ID: 41907601
There should be no values in H at the beginning.  The Value Col. (H) only shows the count up or count down as it happens.

Upon "Restart, or Start, Column H is empty.

B.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Bright01
ID: 41907603
They were in the original WS I sent only to represent what value was in there at "THE END".  Otherwise they would be blank.  Very sorry for the confusion.

B.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41907621
OK, now I understand.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41907718
I didn't add the Restart button, and instead I added code that lets you just press "START" again. To help see what's going on I added code that updates the workbook's status bar (in lower left-hand corner of the workbook) , and I also change the cursor to an hourglass when the code is running.
28986331b.xlsm
0
 

Author Comment

by:Bright01
ID: 41907869
Martin,

The "countdown" in Series 1 and 2 seem to work; but the "countup" in the other series down respond.  Keep in mind, that each of the F and G input cells for Start and End can be either count up or count down depending on if the numbers in F and G are higher or lower in their relationship.

B.
0
 

Author Comment

by:Bright01
ID: 41907870
Not down..... "don't" respond.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41907939
Here's what I got after I clicked START. If it isn't correct then let me know, and if your starting with a different set of data please show it to me.
?
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41907963
Never mind, I found a bug.
0
 
LVL 46

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 41907972
Corrected,
28986331c.xlsm
0
 

Author Closing Comment

by:Bright01
ID: 41908357
Fantastic job Martin.  Works as promised and you just kept on with it until it was perfect.  You are always the professional.  Again, much thanks for hanging with me on this.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41908946
You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2015
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41909526
Currently when you click "START", the chart's "y" axis fluctuates based on the changing values in the chart which I don't think is ideal. If you want it to stay fixed but always be able to contain the largest value then change the ChangeValues macro to this. As an aside you might also want to delete the several commented out lines in both the sheet code and Module1 code  that I forgot to delete.

Sub ChangeValues()
Application.Cursor = xlWait
Range("H12:H16").ClearContents
Worksheets(1).ChartObjects(1).Activate
ActiveChart.ChartArea.Select
ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlValue).MaximumScale = Application.WorksheetFunction.Max(Range("F12:G16"))

StartTimer
End Sub

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
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…

911 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now