Popup Reminder in Excel That Beeps and Speaks - Very Useful

[Music] so [Music] hello everyone i am nabil murad i spend long hours working on excel creating tutorials or delivering courses sometimes i want to set a reminder that pops up in excel to prompt me for doing something like making a phone call joining a meeting sending an email or maybe taking a medicine so i created a pop-up reminder in excel that beeps at the specified time displays any message i want and reads it aloud let me show you how it works so if i go to the view tab i created a special group and the special icon i named it remind me for this functionality if i click i'll be triggering the functionality and an input box pops up and asks me what time would you like the reminder message to pop up right now it's 4 40 pm so i'm going to write 442 pm and then i hit ok another input box pops up asking me please enter the reminder message so i'll be typing it's time for a break pause the recording and make a copy so i hit ok i keep an eye on the time in the lower right corner of my screen when it turns 442 my computer will beep a message box will pop up and it will read the message that i type it's time for a break pause the recording and make a coffee and here is the message box i hit ok i closed the message box and now i'll be creating this useful project from scratch and show you how to replicate it even if you have never used vba before by copying and pasting few lines of code that i'll put in the description below the video so let's get started this is a new blank excel file and i want to create a little code in vba you can simply copy and paste the code in the visual basic editor and to switch to the visual basic editor i hit alt f11 my visual basic editor opens i'm going to maximize it and here i need a module to create my code so i click on the insert menu and i select module alternatively i can use the shortcut alt i m in the new module i'll be declaring two variables at the module level before creating my subroutine the first variable is named alarm dim alarm is double so it allows decimals the second variable is named message the message as string because it's text and then i hit enter and i start creating my subroutine and i'll name it popup reminder any subroutine starts with the keyword sub so i'll be typing sub popup reminder i open and close bracket and then i hit enter the word sub turns blue end sub has been added and between the sub and end sub i'll be writing my subroutine i'll be declaring a variable at the subroutine level and i'll call it when dim when a string i hit enter and i start creating a conditional if statement if the alarm variable is zero then we want to do a few things i will assign a value to the when variable and this value will be through an input box that will be collecting user input when equals input box and in brackets in double quotes i type the message that will appear in the input box what time would you like the reminder message to pop up i hit enter and then i add a second conditional statement assuming that the user didn't provide anything or the user typed anything other than time if when does not equal blank and when does not equal false then what would you like to do i want to assign a value to the second variable message this value will be in a second input box collecting inputs from the user message equals input box and in brackets in double quotes i type whatever i want to appear in the input box please enter the reminder message i hit enter and assuming that i get an error in the next step so i want to provide an error handler on error resume next i hit enter and now i want to give a second value to the alarm based upon what the user provided in the input box alarm equals date plus time value of when whatever the user provides for the when will be added to the date and that will be the set alarm i hit enter and now i want to disable the error handler so i type on error go to zero i hit enter i'll be using the on time method for triggering the subroutine so i type application dot on time space alarm the variable alarm what do you want to do at that time well i want to trigger the subroutine popup reminder but now because the alarm has a value it will not go to the beginning of the if statement it will go to an else part of the if statement so i type end if for the second if statement and else for the first if statement now when i trigger the subroutine popup reminder because alarm has a value based upon the user input it will go directly to the else portion what do you want to happen i want it to beep i want to read the message and display the message box so the first action will be beeping the second action application.speech.speak space message this is the message variable then the application will read whatever the user provided in the message input box and then it will display the message box to prepare for running the code another time i want to clear the message variable and i want to clear alarm variable so i type message equals double quote double quote alarm equals zero and then i close the if statement with an end if and i close the subroutine ends up and i finished creating my code let's test it so i'm going to close my visual basic editor to go back to excel to be able to use the subroutine from within excel i need to add it to the ribbon so i'll be customizing my ribbon and to do that i need to open the excel options dialog box so i click on the file tab and click on options alternatively you can use the shortcut alt ft and i select customize ribbon i select the view tab and expand it select the last option macros and i would like to add another group below macro so i click on new group i want to rename the custom group so i click on rename and i'm going to name it reminder i hit ok and then i would like to add the macro to this group so from the top left drop list i click on the down arrow and select macros here is the only macro i have so i select it and hit add and it will be added to the group i want to rename it give it a user friendly name and give it a nice icon so i click on rename this dialog box opens and i'm going simply to add a space between pop-up and reminder and i'll be assigning this alarm icon and then i hit ok and then i hit ok to close the excel options dialog box if i go to the view tab here is the new group it has been added and now i want to test it right now it's 5 pm so i'm going to click on this icon for the pop-up reminder and i want my reminder to be triggered at 502 so i'll be typing 502 pm and i hit ok now i want to provide the message and the message will read take two pills of the medicine and measure your blood pressure i hit okay for the message and keep an eye on the time in the lower right corner of my screen when it's 502 you will hear a beep the message will be read aloud and you will see the message box take two pills of the medicine and measure your blood pressure i hit ok and i want to test it one last time so i click on the pop-up reminder this time i want to trigger it at 503 and the message will be did you subscribe to my channel the best is yet to come did you subscribe to my channel the best is yet to come i hit ok thanks for watching and see you next time [Music] you

Channel: Officeinstructor

Views: 17,291

Rating: 4.9556541 out of 5

Keywords: Excel, Microsoft Excel, Nabil Mourad, OfficeInstructor, Office, Instructor, See-How, MVP, Problem Solved, Formulas, Functions, Power Query, VBA, ONTime, Alarm, Popup Alarm, Reminder, Speak

Id: qmrMH5UvH3A

Length: 11min 34sec (694 seconds)

Published: Sat Jan 02 2021