MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Tip of The Day Function Sought in Excel!


Posted by Jack J on December 13, 2001 12:25 AM


The Tip Of The Day Function in MSWord is sometimes a pain but then again it can be useful.

I would like to be able to apply this principle to an Excel workbook. What I am after is some code that will either open an Msg message or form, display said message or form for 5 seconds and then close. This procedure would run using the On_Open function event. I have something similar to this already but the procedure only displays a single message. The same message is repeated everytime the workbook is opened. I would like to record up to 31 messages that would display themselves over a month, one per day as the workbook is opened. The message would not repeat itself if the workbook were accessed more than once a day. After 31 days the original message would be displayed again in a loop format. Any suggestions on how best to tackle this problem would be welcomed.

Jack


Posted by Colo on December 13, 2001 1:41 AM

Hi.You mean like this?

Sub test()
Dim msg(1 To 31) As String, i As Integer
'Make sample data
For i = 1 To 31
msg(i) = "This is a sample message #" & i
Next
'show message box
MsgBox msg(Day(Date))
End Sub

Posted by Lepidus on December 13, 2001 1:44 AM


Try this :-

1. On a blank worksheet (let's say named Sheet1), put the numbers 1 to 31 in cells A1:A31.

2. On the same sheet (Sheet1), put your daily tips in cells B1:B31.

3. Hide Sheet1.

4. Put this procedure in the workbook module :-

Private Sub Workbook_Open()
Dim d As Integer, w As Worksheet
Dim r As Range, f As Range
d = Day(Now())
Set w = Worksheets("Sheet1")
Set r = w.Range("A1:A31")
Set f = r.Find(What:=d, After:=r.Cells(31))
With f.Offset(0, 2)
If .Value <> 1 Then
MsgBox .Offset(0, -1)
.Value = 1
w.Range("C1:C" & .Row - 1).ClearContents
w.Range("C" & .Row + 1 & ":C31").ClearContents
End If
End With
End Sub

Posted by Jim on December 13, 2001 5:58 AM

Hi Jack,
Private Sub UserForm_Activate()
Application.OnTime Now + TimeValue("00:00:05") _
"Kill The Form"
End Sub

HTH

Jim

Posted by Jack J on December 13, 2001 12:38 PM

Hi.You mean like this? Dim msg(1 To 31) As String, i As Integer 'Make sample data For i = 1 To 31 msg(i) = "This is a sample message #" & i Next 'show message box MsgBox msg(Day(Date))

Hi Colo

I tried out your code. I placed it in an On_Open event and set it away. It brought up an Msg message and day. I need two further parts to make this work as I had hoped. Firstly a little more insight on how to place additional messages within your code so that they are brought up when the workbook is opened. Secondly, I need the code to recognise that the workbook has been opened more than once and deactivate the pop up messages until the next day is upon us. Then the same rules apply and the message appears only once.

Posted by Lepidus on December 13, 2001 1:47 PM


The suggestion I posted does exactly this.

Posted by Colo on December 13, 2001 5:54 PM

:The suggestion I posted does exactly this.
I think so too.
And about how to close message automatically, you had better use UserForm as Jim wrote.
Please make UserForm "Userform1" with Label "Label1."

Sorry, I am remodeling the code of Lepidus without permission.
I tried this , and it worked on my Excel97.

'/*Paste ThisWorkBokk Module*/
Private Sub Workbook_Open()
Dim d As Integer, w As Worksheet
Dim r As Range, f As Range
d = Day(Now())
Set w = Worksheets("Sheet1")
Set r = w.Range("A1:A31")
Set f = r.Find(What:=d, After:=r.Cells(31))
With f.Offset(0, 2)
If .Value <> 1 Then
UserForm1.Label1.Caption = .Offset(0, -1)
UserForm1.Show
.Value = 1
w.Range("C1:C" & .Row - 1).ClearContents
w.Range("C" & .Row + 1 & ":C31").ClearContents
End If
End With
End Sub

'/*Paste Module1 (or 2 or 3 you want to...) */
Sub Kill_The_Form()
Unload UserForm1
End Sub

'/*Paste UserForm1 Mudule */
Private Sub UserForm_Activate()
Application.OnTime Now + TimeValue("00:00:05"), "Kill_The_Form"
End Sub

Posted by Lepidus on December 13, 2001 7:26 PM

A small point .....

:The suggestion I posted does exactly this.


Rather than a user form which closes automatically after 5 seconds, I think it is simpler(& maybe preferable) merely to have a message box (which the user closes) - per my original posting. Just my opinion (for what it's worth).

Posted by Jack J on December 13, 2001 11:19 PM

Cheers

Thanks for all the info. I think I have enough permutations to take this to fruition.

Jack J