Macro activated after a specified time

[buffalobill]

Hi, please help if you can!

I have a macro that activates another macro after a 5 second wait. On my original sheet I have numerous buttons that activate a macro after a 5 second wait. What I need to do is stop a macro from running if a button is pushed before the previous 5 second wait is up. In other words what is happening is I push one button, if I wait 3 seconds and push another button the first macro will still be running and 2 seconds later the macro will be activated. Sorry if this sounds confusing, I have attached a sample to this message, click on button 1,go back to sheet one, wait three seconds and click on button 2 and you should be able to see what I mean.

Thanks in advance for any help with this.

[6StringJazzer]

To cancel a scheduled procedure, add FALSE as the last parameter. However, instead of simply using Now to determine the scheduled time, you will have to assign the scheduled time to a variable so you can save it.

Please Login or Register  to view this content.

[buffalobill]

Thanks for your reply, could you please clarify what I should do to assign the scheduled time to a variable? Sorry I'm a newbie at excel and still very much in the learning process.

[6StringJazzer]

Well, I admit I am a little confused about what you're trying to do, but see the attached.

Using my modification, if the user pushes Button 1, and then pushes Button 2 before 5 seconds have elapsed since the press of Button 1, then the Clear event scheduled by Button 1 will be cancelled, and a new Clear event will be scheduled upon the Button 2 press.

If that's not exactly what you meant then perhaps this will at least show you how it works. I declared a new variable above the Subs. This means that this variable is static--once its value is set, it continues to show the same value, no matter which Sub references it. Variables declared inside Subs are local to the Sub, and do not exist anymore once that Sub stops running. One way for Subs to communicate data among themselves is to use static variables. It's generally better to pass data as parameters, but in this case I think the static variable is a better choice (maybe the only choice).

So this new variable is used to capture the time of the future event you want to schedule. That way the most recently scheduled event time is always available to all Subs.

Although what you are doing is relatively simply, event scheduling can be a very complex topic.

[buffalobill]

Hi, thanks but it still doesnt seem to be working the way I need it to work in your example (I think you understand what I need it to do but for some reason it is still not deactivating the macro). I have attached my whole project to this message, you will need to create a folder on your desktop called "Pager" and save the word document in there for it to work. (this project is designed to work with dual monitors as it will have two programs open at the same time i.e.word & excel)

What I have is a vehicle storage yard, one monitor will be located inside the storage area for the staff to view (the word document) the other monitor will be at the serving desk. When a customer collects their vehicle the button with the bay number will be pressed on the spreadsheet, this will tell the staff in the storage area exactly where the vehicle is located, the sheet is set to stay displayed for ten seconds at present and then changes to a sheet saying "No Customers".
What I need is the last button that is pressed should result in the page being displayed staying visible for 10 seconds before changing to "No Customers", and not any less if another button was pushed and is still counting down its ten seconds.

Thanks for your help so far on this problem, I really do appreciate it.

[6StringJazzer]

First, here's your prototype updated. It seemed to work fine for me in the push-button-1-then-push-button-2 scenario. However, I introduced a bug such that a press of Button 2 all by itself generated an error because it tried to cancel an event scheduled by Button 1, but Button 1 hadn't been pressed, so the event did not exist.

I also added a little event log to capture the sequence of events that occurs. As you can see, if you press Button 1, a Clear is scheduled for 5 seconds thence. If you press Button 2 prior to when that 5 seconds is up, then it cancels the first Clear and schedules a new one.

Next I will tackle your complete package.

[6StringJazzer]

...this will tell the staff in the storage area exactly where the vehicle is located, the sheet is set to stay displayed for ten seconds at present and then changes to a sheet saying "No Customers".

I am still cogitating on this problem, but I have to ask--what happens if the bay number is shown in the storage area, but 10 seconds pass before anyone actually sees the screen? What happens if the guy is on a bathroom break? That's outside the scope of your question but I have a software development background and have done a lot of requirements work, trying to make sure that your application actually meets the requirements of the real-world environment. I would require the person in the storage to acknowledge the call somehow before clearing the screen.

[buffalobill]

That is fantastic, its doing exactly what I need it to do...you are a genius. Now I just have to see if Im clever enough to intergrate that into my project....wish me luck.

In regards to how the system will work, I have got it playing a sound to alert the staff that a customer is coming through to collect their vehicle as well as using the speak function to anounce the bay number where the car is, I also plan on increasing the time limit to 30 seconds, only had it set at 10 seconds for the sake of making it easier to work with at the moment, I have 5 staff members in the storage area at any one time so there should always be someone within 10 -15 seconds away from the monitor.

Thanks again for all your help on this!

[6StringJazzer]

Here is a version of your setup that works the way you have defined.

Note that I have made some extensive changes to simplify your code without changing the basic structure. A few tips:

When you record a macro, Excel records every individual step, such as selecting a cell with the cursor. But in reality, you do not have to select a cell to be able to refer to the data in it. For example:

Please Login or Register  to view this content.

Also, if you leave out the Selects, your user doesn't see any screen activity, and things are sped up considerably.

Also, the code for nearly all your button presses is exactly the same, except for the address of the cell that contains the hyperlink and the address of the cell that contains the text that will be spoken. I have created a single Sub with two parameters for those two things. Then your button handlers simply call this Sub. Next time you need to make a change, you can make it in one place instead of 35 places.

I also renamed your modules to reflect their purpose, and moved all the button handlers into the same module.

I added a worksheet called Log that will log everything that happens. It is primarily to show you what the code is doing, but you may want to keep it around anyway. If not you can just delete the sheet, and remove the lines of code that start Log.LogEntry (they will cause errors if the worksheet Log doesn't exist).

In PlayWavFile, I suggest you use a different name other than Wait for your parameter, which is the same name as the procedure Application.Wait. It works fine but can be confusing.

Similar issue with your Sub called Time.

Phew! If this works you owe me a Foster's, mate!

[buffalobill]

WOW, thanks mate, this is awesome, can't believe how quick you got all that done, it works like a dream, as for that Fosters, next time you'r in Oz I'll get you a whole case.

ps. thanks for all the tips, i'll definatly take them on board

pps. Thanks again Mate!!!