Tip of The Day Function Sought in Excel!

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

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

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

HTH

Jim

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.

The suggestion I posted does exactly this.

: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

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).

Cheers

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

Jack J