Dynamic Excel Countdown Graphic

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
Bright01Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Martin LissConnect With a Mentor Older than dirtCommented:
Corrected,
28986331c.xlsm
0
 
Martin LissOlder than dirtCommented:
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
 
Bright01Author Commented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Martin LissOlder than dirtCommented:
Yes it does.
0
 
Martin LissOlder than dirtCommented:
Try this.
28986331.xlsm
0
 
Martin LissOlder than dirtCommented:
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
 
Bright01Author Commented:
Martin,  You are "the Master"!  One tweek if you don't mind; can we add a "reset button so it starts over?

B.
0
 
Martin LissOlder than dirtCommented:
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
 
Bright01Author Commented:
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
 
Martin LissOlder than dirtCommented:
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
 
Bright01Author Commented:
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
 
Bright01Author Commented:
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
 
Martin LissOlder than dirtCommented:
OK, now I understand.
0
 
Martin LissOlder than dirtCommented:
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
 
Bright01Author Commented:
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
 
Bright01Author Commented:
Not down..... "don't" respond.
0
 
Martin LissOlder than dirtCommented:
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
 
Martin LissOlder than dirtCommented:
Never mind, I found a bug.
0
 
Bright01Author Commented:
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
 
Martin LissOlder than dirtCommented:
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
 
Martin LissOlder than dirtCommented:
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
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.