Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Is it possible to run multiple worksheet macros at the same time on the same worksheet with Excel

Posted on 2011-02-15
14
Medium Priority
?
704 Views
Last Modified: 2012-05-11
I have 12 countdown timers on the same worksheet to be used in front of fryers in our restaurant.
Is it possible to run multiple ones at the same time.  Currently each macro is tied to each Start command button and is stopped by it's own Stop command button.
0
Comment
Question by:RogerKMiller
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 4
  • +1
14 Comments
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 34902403
Not that i know.   But you could simply run a procedure on Application.OnTime every second, and in this VBA function, you can check if the Start Button have been pressed for a given counter, and decrease it if necessary.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 34902444
RogerKMiller,

Couple of options...
 - Start excel 12 times and have each version run a separate time. (No, I don't think much of that option, either.)
 - Not absolutely sure about this, but... Use a single "master" macro, have it sleep for a second, with DoEvents enabled. Have an Event so that a user action is trapped (e.g. selecting a a timer's "Start" cell). The event then updates a hidden cell value changing the status of that timer. A second later the master timer wakes up, reads the hidden cells and updates timers accordingly.

Regards,
Brian.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 34902454
Apologies, cdebel, crossing posts.

Regards,
Brian.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 34902533
(no prob Redmondb)

Here's an example of what i just talked.

I've just done 2 timers in this version, but you can put as much as you want.
CountDown.xlsm
0
 
LVL 26

Expert Comment

by:redmondb
ID: 34902646
Very nice, cdebel. Shorter and sweeter!

Regards,
Brian.
0
 

Author Comment

by:RogerKMiller
ID: 34902783
cdebel:
When opening it, I guess because I have 2003, it says "Can't exit Design Mode because Control 'btnStart1 can not be created.  Also I tried to view the code for the buttons but couldn't get anywhere.  Looks good if I can understand how it works.  Can you perhaps send it in an earlier format?
Thanks,
Roger

0
 
LVL 42

Expert Comment

by:dlmille
ID: 34902879
How about this?  Its a free download and you can run multiple instances...  These are countdown timers.

http://www.downv.com/Windows/download-AleJenJes-Countdown-Timer-10280820.htm

Dave
0
 

Author Comment

by:RogerKMiller
ID: 34902930
dlmille:
the kitchen one looked like it might work but it wouldn't download.  It went thru all the motions but didn't download.  I need to make these editable with the name of a food item, like French Fries and then have a countdown timer beneath it or next to it with a start and a stop button.
0
 
LVL 10

Accepted Solution

by:
Christian de Bellefeuille earned 2000 total points
ID: 34903097
Redmond:
Here's the same thing in Excel 2003 format.
You have to check for the VBA Editor to see the code behind...

There's a module "Module1" where i've placed this peace of code.  It check if you pressed on a start button for a given timer.   And it decrease that timer by 1 second.  It also recall itself every second.

Public bStarted(12) As Boolean

Private Sub CountDownTimers()
    Dim d As Date
   
    If bStarted(0) Then
        d = Sheet1.Cells(5, 1)
        Sheet1.Cells(5, 1) = d - TimeValue("00:00:01")
    End If

    If bStarted(1) Then
        d = Sheet1.Cells(5, 2)
        Sheet1.Cells(5, 2) = d - TimeValue("00:00:01")
    End If
    Application.OnTime Now() + TimeValue("00:00:01"), "CountDownTimers"
End Sub


There's also this piece of code which trigger the initial call to the CountDownTimers.  It happen on Workbook open event as you can see:
Private Sub Workbook_Open()
    Application.OnTime Now() + TimeValue("00:00:01"), "CountDownTimers"
End Sub



And finally, this piece of code just set to True the array where it memorize if the counter is counting or if it's stopped:

Private Sub btnStart1_Click()
    bStarted(0) = True
End Sub

Private Sub btnStop1_Click()
    bStarted(0) = False
End Sub

Private Sub btnStart2_Click()
    bStarted(1) = True
End Sub

Private Sub btnStop2_Click()
    bStarted(1) = False
End Sub



CountDown.xls
0
 

Author Comment

by:RogerKMiller
ID: 34903132
Looks great!
Thanks,
Roger
0
 
LVL 26

Expert Comment

by:redmondb
ID: 34903154
cdebel,

Small correction..."RogerKMiller" :)

Cheers,
Brian.
0
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 34903157
oops :D
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34903346
works for me.  glad you got a solution!

Dave
0
 

Author Comment

by:RogerKMiller
ID: 34903352
Thanks,
Roger
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

618 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