Consulting

Results 1 to 12 of 12

Thread: reminder box

  1. #1

    reminder box

    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?

  2. #2
    i meant to say Outlook, not access.
    thanks!

  3. #3
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Here's one method. The time is checked when Excel is opened.
    Place this code in your monitored workbook ThisWorkbook module
    [VBA]Private Sub Workbook_Open()
    SaveSetting "MyExcelFile", "LastOpened", "Date", Now
    End Sub
    [/VBA]
    Place this code in ThisWorkbook module of Personal.xls
    [VBA]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
    [/VBA]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Why n ot do it in Outlook? It has built-in functionality.

  5. #5
    we don't use outlook.

  6. #6
    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.

  7. #7
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    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
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  8. #8
    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?

  9. #9
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Personal.xls:
    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
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  10. #10
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  11. #11
    is it not possible to just include it in the excel file that will be sent to each user?

  12. #12
    i guess i see what you are getting at. excel must be active for the code to work?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •