Ever miss a deadline and want to kick yourself for it? I have. One thing I know about myself is that when I get focused on one thing, I miss other things.
I use reminders and alarms liberally, both on my computer and on my phone. When I'm not busy, these tools work great. But when I get engrossed in something, it becomes much more likely that I'll miss them.
Toward that end, I've been thinking about setting up Excel to be rather annoying, to the degree that I can't ignore it.
Let me explain.
Let's say I have an Excel file that I have to upload to a specific folder by noon each day. It might be some sort of transactions, like billable invoice data, and I need to get the file into the correct location before some automated process picks it up.
There are many ways I can set myself a reminder, of course, but for today's discussion, let's say that I want this particular reminder to come up on my Excel window. I might do this because Excel is where I tend to spend most of my time, or because Excel is where I tend to get lost in thought.
I recently wrote about creating a timer in Excel (here), and running a macro when that timer goes off. I'm going to expand on that here. This time, I'm going to set the timer to issue a warning when I've got 10 minutes left to complete my upload. For that last 10 minutes, I'll have the computer beep at me every minute, to make sure I don't get sidetracked.
Sounding the Alarm
I need to add three blocks of code to my Excel file to accomplish this task.
The first goes in a standard module, and it looks like this:
Here are the keys to that block of code:
I've got a variable at the top called "NextRunTime". This variable is used to control when my timed procedure will run next.
In the TimeCheck procedure, I set up the parameters for my alarm - it'll go off between 11:50 am and noon today.
What those conditions are met, I'll highlight my heading row in yellow (to give a visual reminder), and I'll tell the computer to beep.
Finally, I set up the procedure to run again one minute later. This procedure runs once every minute whenever this file is open.
See that "Beep" command? That's all it takes to make the computer beep. It's a built-in command, so I don't need to do anything to make it work. I simply issue the "Beep" command, and the computer beeps.
Kicking Things Off
To complete this solution, I add two additional blocks of code to my Workbook module. These procedures kick off the TimeCheck routine when the file is opened, and shut it down when the file is closed. They look like this:
Pretty simple, right? For a deeper dive on the timer code, refer back to my post on Scheduling Excel Macros with a Timer.
For reference, here's what my worksheet looks like before 11:50 am:
And after:
So there you have it.
I've set up Excel to nag me for that last 10 minutes before my file needs to be uploaded. Of course, it goes without saying, use this judiciously, so that it doesn't become annoying.
Need help with your timer? Reach out to me via email or phone to discuss your solution, whether you need help troubleshooting, or would like me to set up your timer.
In my next Excel post, we'll talk about creating a stopwatch in Excel, which is similar to what we've been working on with timers. To be notified when that post goes live, subscribe using the form below.
Kim
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of IT experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
© North Port Solutions, LLC, 2023. All rights reserved.