Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Las Vegas, Nevada, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Message depending of day (XP)

    Is it possible to have a macro insert text into a text box depending on the day of the week?
    The macro (to run on open) would compare day of the week against the computer date and insert text
    from another excel file based on the day (i.e. Tuesday).
    The list would show different tasks to be inserted and displayed in the text box.
    The day of the week would be in "A" column and text would be in "B" column.

    1 Monday -- Check last week
    Richard Spring

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Message depending of day (XP)

    If the messages are on the sheet named Sheet2 in the range B1:B7, then try:

    <code>
    MsgBox Worksheets("Sheet2").Range("B1").Offset(Weekday(Da te, vbMonday) - 1, 0)
    </code>
    Legare Coleman

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Message depending of day (XP)

    You can do the following, without needing any code:
    - Create a workbook Tasks with a table like this in a sheet named Sheet:

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><tr><td align=center>1</td><td align=right>1</td><td>Check last week

  4. #4
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Las Vegas, Nevada, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Message depending of day (XP)

    Wow - Instant response. You DO know this is Saturday!
    Legare: I tried your code and it returned a text box with "Saturday" and not "What are you ......". Should it return the text in C column?

    Hans: Will check out your solution.

    Thanks
    Richard Spring

  5. #5
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Las Vegas, Nevada, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Message depending of day (XP)

    Hans: Worked through your suggestion. Took a few tries, but it did work. <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

    If I change the "B" column text on the Tasks workbook it should show the changes
    the next time that day comes around. Correct?
    Richard Spring

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Message depending of day (XP)

    Yes, if you have set the workbook to update links automatically.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Las Vegas, Nevada, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Message depending of day (XP)

    Hans: Yes, if you have set the workbook to update links automatically.

    How do you set the workbook to update links automatically?
    Not sure how to do that one.
    Richard Spring

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Message depending of day (XP)

    1) Select Edit | Links and make sure that the link is set to Automatic.

    2) Select Tools | Options...
    Activate the Edit tab.
    Clear the check box 'Ask to update automatic links'.
    Click OK.

  9. #9
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Las Vegas, Nevada, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Message depending of day (XP)

    Thanks Hans, I will setup the automatic links.

    I figured out why the code did not work. I changed the "0" at the end of the line to a "1" and it works perfectly.

    MsgBox Worksheets("Sheet2").Range("B1").Offset(Weekday(Da te, vbMonday) - 1, 1).
    Richard Spring

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Message depending of day (XP)

    It should return the text in the cells B1:B7 on Sheet2. If you want the text from column C, then change the B1 in the line of code to C1.
    Legare Coleman

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Message depending of day (XP)

    My code does not need a coulum with the day names or day numbers. It just needs a column with the seven messages. If the messages are in column C, then you can change the code to:

    <code>
    MsgBox Worksheets("Sheet2").Range("C1").Offset(Weekday(Da te, vbMonday) - 1, 0).
    </code>
    Legare Coleman

  12. #12
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Las Vegas, Nevada, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Message depending of day (XP)

    MsgBox Worksheets("Sheet2").Range("C1").Offset(Weekday(Da te, vbMonday) - 1, 0) works with or without the days in column B. <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

    Does Excel assume since there are seven items that it refers to the days of the week?

    If the message box message is fairly long, is there a way to force a paragraph or line break?

    The answers are always there - you just need to ask the right question!
    Richard Spring

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Message depending of day (XP)

    In your source you can add <alt><enter> to break the text up where you want the breaks to be

    Steve

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Message depending of day (XP)

    The Weekday function returns the day of the week number of 1 to 7, with vbMonday as the second argument Monday is 1. I us that number minus 1 in the offset method to get the text from the appropriate cell. Exel does not assume anything.

    If you hold down the Alt key and press enter in the message box text you will insert a line break into the text.
    Legare Coleman

  15. #15
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Las Vegas, Nevada, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Message depending of day (XP)

    Thank you Hans, Legare, and Steve.

    As usual your expertise shines through.
    I learn each time and hopefully increase my knowledge. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    The answers are out there - you just need to ask the right question!
    Richard Spring

Posting Permissions

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