Solved

Dynamic Excel Countdown Graphic

Posted on 2016-11-30
21
17 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 45

Expert Comment

by:Martin Liss
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
Yes it does.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Try this.
28986331.xlsm
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
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
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Bright01
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
OK, now I understand.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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
Comment Utility
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
Comment Utility
Not down..... "don't" respond.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
Never mind, I found a bug.
0
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
Comment Utility
Corrected,
28986331c.xlsm
0
 

Author Closing Comment

by:Bright01
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
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…

762 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

10 Experts available now in Live!

Get 1:1 Help Now