Creating a Digital Clock #2 – sprite driver, timing macro and functions, full model

Hi guys, here is the final part of tutorial about creating a digital clock in Excel. It is a good tutorial about general use of sprites in animation, especially the first half. Tell us your thoughts. George

[sociallocker][/sociallocker]

This article has 50 Comments

  1. Dear Sir
    I wish to create a digital clock but I can’t adjust both axes to range between -5 and +5, how can I do it.

  2. Hi! with your help, i get things done. thank u. Keep up!!!!

  3. Virgile, Right click the button and see which macro is attached to the button. Also just open the VBA editor and poke troug the macros, that macro must have a similar name (or the same) with the button.
    It must be a “count up” type of macro.

  4. Hi! may be i’m not expressing well what is my prob. in your digital clock(excel, sheet Digital Clock_2), i found no spin button needed to be pushed to display the digit, they seem to be displayed automatically. the only thing the user do is to press on Run-Pause, and digit get displayed according to time. How to do exactly the same?

  5. Watch wat the spin buttn does and the if() functions hat change the coordinates of the sprites. All stay out but only one sprite comes in the view.

  6. Thank u. what i don’t know exactly, is the way to bring them up at different moments in time. in tutorial, the example given does not explain it well: the user press Spin button, to display one digit. how to make it so that digit (sprites) display itself when time change? i mean to have exactly the same digital clock as the one you designed.

  7. Sprites don’t change, you have an army of sprites of different shapes and sizes and bring them up at different moments in time, one at a time. During this time you keep the rest hidden.

  8. Hi! i followed your tutorial and appreciate your work but i still not understand how sprites will be changing as the time change i.e to connect the sprites generated with time. Please help me to solve that issue!

  9. Hi Greg, sorry for the late reply, insert the statement “DoEvents” liberally within your main macro when things don’t update or the program is kind of non responsive. Look in my code and add more line with this statement (DoEvents). Experiment adding this statement every other line and then start removing most of them while making sure the program maintains its functionality.

  10. George this was an awesome resource. I was able to design the clock. My problem is that when I click the Start/Stop button my clock won’t stop running.

    I can open the file, enable the macros, and start the clock. But when I click stop, the clock continues to tick and then Excel crashes! Any suggestions on what could be going wrong? I double checked the code and it matches yours.

    Aside from that your info was very helpful!

  11. Hassan, Make sure you use “DoEvents” as often as you can in your macro. Look at my code and see that I use “DoEvents” liberally. In 2007 or 2010 you need to use it even more often to allow for chart refreshing than in 2003.

  12. i somehow cannot associate the button to the table 🙁

  13. George it was a pleasure going through your website.

    i am stuck. need help. i am looking for a 3 or 4 digit display. i have been able to create the button, assign macro to it. but the changes in the table are not appearing. how do you “Verify the functionality of the button-macro pair.” ???

    regards
    Hassan

  14. I hate those chats. I prefer Skype or phone. My handle is: ge-lun and my # is 719-373-0680.

  15. do you got anything like msn or something so we could chat?

  16. No problem, you are welcome. Take everything on this site as a tease. Be original/creative, the math and the syntax should be secondary.

  17. george thanks you brought me a new idea thanks oooh i hope we could keep chatting with some site so you can help me if i need i hope that you want that

  18. I was a lazy student myself when help was close. Try to get a few ideas from the blog and do something new and much better yourself (not a clock, the clock is just a teaser or a starter). Make some sort of shooting game or some strategy game, very simple at first and add someting every day to it till it becomes very cool. You know how to introduce sprites, move them around and hide them. It’s all there (check other models too). Good luck!

  19. i am now at page 10 🙂 thanks for the help
    page 10 takes looooooooooong

  20. I am not sure what you mean. Try to be more thorugh.
    My impression is that you need a nanny. You need to spend some time with this. If you proceed like in school (get help from colleagues or the teacher every time you get stuck) you won’t ever be independent.

  21. ok i got that but when i am on page 7 and i place the codes on the good places and stuff then my digit goes away and when i try to fix it again then it don’t work only if i go to 0 then i got the digit 9 and only that ones working

  22. can i just copy the digit then place it on the dot on the middle of the chart area then i’m done?

  23. And of course you can replace the other 2 points with two other shapes by using the same recipe. After that you can use VBA to change the coordinates and create some sort of “puppet show” where all the points except one is in the range of the axes (visible) and the restare far away to the left for instance(say -999,0). We call these shapes (or puppets) sprites. You can move within the chart window or you can send far away hence they become invisible.

  24. Let’s say for the sake of argument you have 3 points on a scater chart (0,0), (1,1), (2,2). These points can be connected with a 45 degrees straight line. You want to paste 3 different shapes/pictures on them (replace each marker with a differnt shape). You choose a picure or a shape, highlight it and click “copy”. Now go the the chart, click once on any marker and “paste” You end up with the shape in the place of each marker, this means you now see three identical shapes centered at coordinates: (0,0), (1,1), (2,2).
    If you want for instance to to leave 2 markers the same and replace only (1,1) marker with the shape, you need to copy the shape, then click marker (2,2) then wait 2 seconds and then click the same marker again and then “paste”. This way what you end up is the shape centered on coordinate (2,2) and original markers left on the other two points.

  25. what’s a desired marker i only got plot area and Series “Y” Point “0” (0.0)

  26. What’s a desired marker can you say that?

  27. The tutorial is for the old Excel (2003). In the new one first copy the digit shape then just select the desired marker by clicking it, wait 1 or 2 seconds and now click the marker again. After the second click only one marker will be selected (the rest are unselected). After that, just “right click” and “paste”. The marker will be replaced with the copied object (in this case it is a clock digit).

  28. ok i got an scatter i made the x and the y -5 to 5 and i deleted everyting i only got an white square with an square that is 45 degrades turned now i don’t know how to do this:
    – Click on the data point on the screen then
    wait 1-2 seconds then click again until the
    cursor looks like a cross
    – Paste. After that we can see a “0” on the
    chart

  29. You need to tell me exactly where you are and what does not work in 2010. It’s true that this tutorial was made for 2003.

  30. ok i am now father but now i need a scatter chart and how to change it

  31. the part on page 2 with that code don’t work can you help me i got excel 2010

  32. Nice ->Work -> Friend…
    I Really Appreciate It.

    Azhar

  33. Very Nice work, really appreciate it

    -Ramprasad

  34. Joel,

    What I meant is to select the sprite “zero” by clicking on it and then holding the shift button down, right click and go through the series of menu options Edit-copyPicture-AsShownOnScreen-OK. After this you will have the picture copied on the clipboard (but nothing happens yet). Then you go to one chart point of your choice and click it. The series on the chart will select. Wait for 1-2 seconds and click again on the same point and now only that particular point will be selected but not the rest. Right click on the top of the point and “paste” or just use the Control “V” shortcut.
    A helping video is here (not so complete but it should give you an idea):
    http://www.youtube.com/watch?v=GS5EgxCYQ_8&feature=plcp

  35. Good day.
    Im having difficulties to insert the sprites, I tried to follow this instruction

    “Select sprite “0” -> Shift -> Edit -> Copy Picture -> As Shown On Screen -> OK”

    but failed. If not a burden to you, can you simplified instruction on how to do it?
    Best Regards,

    Joel

  36. Good day.
    Im having difficulties to insert the sprites, I tried to follow the instruction but failed. If not a burder to ypu, can you simplified instruction on how to do it?
    Best Regards,

    Joel

  37. Tabi, what version of Excel are you using? I am pretty sure it has to do with the security level being too high.

  38. i also appreciate the good work u have done. but i am facing the same problem like Shafiullah. for mine , at the begining while creating the VBA, there is a mesage that displays. “no object found”, pliz help me

  39. nice work , thank you so so much you helped me for what I was looking for . thank you for your efforts .

  40. Shafiullah, Let’s see what I can do in the little time I can scavenge. Thanks, George

  41. Dear Sir,
    I appreciate your hard work regarding the creation of the digital and the analog watches in excel. But your toturial are not enough for any one to creat a clock himself. I request you to make a video with details and tell us how to make everything. I am trying to create both kinds of clocks but in vain. I can’t animate the digits on the face of the scatered chart. Please send me the whole details.
    Thanks.

  42. Now I got it. I was so confused when I saw it at first… thanks to your explanation ı’m cool with it.

  43. Hi Menezes,

    In Excel 2007 you need to do this:

    -Bring up the developer tab: MS Office Orb -> Excel Options -> Popular -> Show the Developer Tab in the Ribbon

    – Bring up the VBA editor: Developer -> Code -> Visual Basic or by just typing Alt+F11
    – Enter design mode : Controls -> Design Mode
    – Insert a “spin button”: Controls -> Insert -> ActiveX Controls -> Spin Button
    – Right click button -> Properties -> change colors to your preference and change name to “UpDown” – or something you like
    – Right click button -> Properties -> Change: Min=1, Max=10
    Double click the button and you’ll see the following code in the VBA editor:

    Private Sub UpDown_Change()
    End Sub

    – Now change the code to your preference for instance:

    Private Sub UpDown_Change()
    Range(“A1”) = UpDown.Value
    End Sub

    – After that don’t forget to exit design mode : Controls -> click Design Mode (before being able to use the button)

    Best regards, George

  44. Hi Menezes,

    In Excel 2007 you need to do this:

    -Bring up the developer tab: MS Office Orb -> Excel Options -> Popular -> Show the Developer Tab in the Ribbon

    – Bring up the VBA editor: Developer -> Code -> Visual Basic or by just typing Alt+F11
    – Enter design mode : Controls -> Design Mode
    – Insert a “spin button”: Controls -> Insert -> ActiveX Controls -> Spin Button
    – Right click button -> Properties -> change colors to your preference and change name to “UpDown” – or something you like
    – Right click button -> Properties -> Change: Min=1, Max=10
    Double click the button and you’ll see the following code in the VBA editor:

    Private Sub UpDown_Change()
    End Sub

    – Now change the code to your preference for instance:

    Private Sub UpDown_Change()
    Range(“A1”) = UpDown.Value
    End Sub

    – After that don’t forget to exit design mode : Controls -> click Design Mode (before being able to use the button)

    Best regards, George

  45. My name is Menezes. I was trying to follow your tutorial digital clock but i could not understand how to Create a spin-button with the range -1 to 10 so i stopped there. Can u give me some clues. In the tutorial you do not say much. Please can you make one tutorial about how to create moving shape in your blog there is only excel sheet without the tutorial. Thanks a lot . This blog is doing a lot to me. By the way, I am using excel 2007.

  46. vezi ca tu nu ai 301 redirect si-ti pierzi din ranking…nu am gasit un buton de contact, da-mi un email si-ti spun ce sa faci.

    Bafta

Leave a Reply

Your email address will not be published. Required fields are marked *