[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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
?
727 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
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
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…
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…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

872 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