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
561 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:cdebel
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
 
LVL 10

Expert Comment

by:cdebel
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 41

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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:
cdebel earned 500 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:cdebel
ID: 34903157
oops :D
0
 
LVL 41

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

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

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

760 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

19 Experts available now in Live!

Get 1:1 Help Now