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
578 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to read all the sheets in excel 12 31
Office 2016 Excel Issue 4 26
ADD New Entries 7 15
Update As Well As Add 6 35
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

919 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

18 Experts available now in Live!

Get 1:1 Help Now