View Full Version : reminder box

03-27-2007, 12:18 PM
i was wondering if it is possible to have a reminder box pop up to inform the user that they need to complete a survey. i have searched the forum but all i can find is stuff with access. i would like to have something where after the initial opening of the excel file if it has not been opened in 48 hours a msgbox will open notifying the user. is this possible to do using just excel? if so does anyone know how?

03-27-2007, 12:19 PM
i meant to say Outlook, not access.

03-27-2007, 12:52 PM
Here's one method. The time is checked when Excel is opened.
Place this code in your monitored workbook ThisWorkbook module
Private Sub Workbook_Open()
SaveSetting "MyExcelFile", "LastOpened", "Date", Now
End Sub

Place this code in ThisWorkbook module of Personal.xls
Private Sub Workbook_Open()
Dim CheckTime As Date
CheckTime = GetSetting(appname:="MyExcelFile", Section:="LastOpened", Key:="Date")
If Now - CheckTime > 2 Then MsgBox "Please open MyExcelFile"
End Sub

Bob Phillips
03-27-2007, 01:07 PM
Why n ot do it in Outlook? It has built-in functionality.

03-28-2007, 06:06 AM
we don't use outlook.

03-28-2007, 06:36 AM
thanks for your reply...
so, should the above two codes be put in different modules then? and the "MyExcelFile" i will have to change to my workbook name?
thanks again.

03-28-2007, 06:56 AM
Yes change the MyExcelFile to your filename but...
read again where Malcolm tells you to put the code....
the first bit of code goes in the file MyExcelFile..
the second bit goes in your personal.xls
both bits go in the thisworkbook module of their respective workbooks. Not in a standard module

03-28-2007, 07:07 AM
sorry i'm still a bit confused. i am not seeing the difference in the two ThisWorkbook modules. the first one i recognize, but would the second one he mentioned be in my Form code?

03-28-2007, 07:13 AM
From John Lacher's site:

Personal.XLS - Your Own Personal Worksheet
Personal.XLS is a special name- when you include a Personal.XLS workbook in Excel's XLSTART directory, every time you open Excel, the Personal.XLS workbook will open automatically. If you use Windows/Hide to hide the window that contains Personal.XLS, then it will open quietly - it will be there but you won't see it - unless you choose Windows/Unhide.
So...what's the good of having Personal.XLS quietly lurking in the background? Well, the first benefit is a place to store frequently used macros. The macro recorder has an option to store recorded macros in Personal.XLS - or you can unhide it and copy or type macros directly to it.
If you have a list of information you need to update or access, Personal. XLS may be the place. You can create worksheets in Personal.XLS and set up a macro to use the built in Data/Form to search and update the data. Anytime you are working in Excel, press the shortcut keys to run a macro that will make your Personal.XLS information available.

It will basically hold any code that you might wish to run on any open document and if you don't already have one you can create it by recording a simple macro then you can unhide it and delete the recorded macro and paste Malcolms code into the thisworkbook module of the newly created personal.xls

03-28-2007, 07:15 AM
My "solution" may not be applicable to your situation. It would require part of the code to be installed on each users' PC. On the other hand I can't see how this would be accomplished other than by writing code to a users PC. Maybe someone else can think of something.

03-28-2007, 07:30 AM
is it not possible to just include it in the excel file that will be sent to each user?

03-28-2007, 07:32 AM
i guess i see what you are getting at. excel must be active for the code to work?