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
631 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: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
Industry Leaders: 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!

 
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 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:
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 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying 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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
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.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

749 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