macro not running on workbook open

[Nick Smith]

For some reason this macro doesn't run on workbook open (or if it does it's
not doing what it's suppoed to do).
It works fine if manually run through the tools menu, but even though it is
written in the ThisWorkbook module, no joy. [note, Cell C1 contains the
formula =today()]. The only think I can think is that the =today() formula
in Cell C1 hasn't calculated prior to the macro running (as it runs on
workbook open) so there is no data to conduct the comparison. If so, how can
I incorporate the =today() function in the macro itself and then conduct the
textual day comparison, i.e. if today is "Thu" and Cell F28 contains "Thu"
then returns MsgBox with text from cell C28.

Sub ReminderMessageOnOpening()

Dim ReportType As String
Dim TodaysDay As String
Dim ReportDueDay As String
Dim i As Integer

For i = 28 To 33
If ThisWorkbook.Sheets("Instructions").Range("C" & i) <> "" And
ThisWorkbook.Sheets("Instructions").Range("F" & i) <> "" Then
TodaysDay = Format(ThisWorkbook.Sheets("Instructions").Range("C1"),
"ddd")
ReportDueDay = ThisWorkbook.Sheets("Instructions").Range("F" & i)
ReportType = ThisWorkbook.Sheets("Instructions").Range("C" & i)
If ReportDueDay = TodaysDay Then
MsgBox ("REMINDER ..... REMINDER ..... REMINDER " & vbNewLine &
ReportType & " due " & ReportDueDay)
End If
End If
Next i
End Sub

Any help appreciated!

Thanks,

Nick

 

[NickHK]

Nick,
Depending on what you have in these cells, I see you are formatting one, but
not the other:
TodaysDay = Format(ThisWorkbook.Sheets("Instructions").Range("C1"), "ddd")
ReportDueDay = ThisWorkbook.Sheets("Instructions").Range("F" & i)
So does this ever become true :
If ReportDueDay = TodaysDay Then

NickHK

P.S.
You will you code more readable by using "With". e.g.
With ThisWorkbook.Sheets("Instructions")
....etc

 

[paul.robinson]

Hi
In the Thisworkbook code module have you done

Private Sub Workbook_Open()
ReminderMessageOnOpening
End Sub

with the ReminderMessageOnOpening sub in a normal code module? You need
to run your sub inside the Workbook_Open event.
If you have done that, then are you opening the workbook manually or
using code? If using code, then the WorkBook_Open event won't fire and
you will have to run your sub from the workbook containing the code.

regards
Paul

 

[Nick Smith]

Thanks Paul - that solved it! Lesson learned....

 

[Nick Smith]

Yes it does become true. Received another point re using Private Sub
Workbook_Open() event..... so solved now. Thanks for the tip re With.

Nick,
Depending on what you have in these cells, I see you are formatting one, but
not the other:
TodaysDay = Format(ThisWorkbook.Sheets("Instructions").Range("C1"), "ddd")
ReportDueDay = ThisWorkbook.Sheets("Instructions").Range("F" & i)
So does this ever become true :
If ReportDueDay = TodaysDay Then

NickHK

P.S.
You will you code more readable by using "With". e.g.
With ThisWorkbook.Sheets("Instructions")
....etc

For some reason this macro doesn't run on workbook open (or if it does it's
not doing what it's suppoed to do).
It works fine if manually run through the tools menu, but even though it is
written in the ThisWorkbook module, no joy. [note, Cell C1 contains the
formula =today()]. The only think I can think is that the =today() formula
in Cell C1 hasn't calculated prior to the macro running (as it runs on
workbook open) so there is no data to conduct the comparison. If so, how can
I incorporate the =today() function in the macro itself and then conduct the
textual day comparison, i.e. if today is "Thu" and Cell F28 contains "Thu"
then returns MsgBox with text from cell C28.

Sub ReminderMessageOnOpening()

Dim ReportType As String
Dim TodaysDay As String
Dim ReportDueDay As String
Dim i As Integer

For i = 28 To 33
If ThisWorkbook.Sheets("Instructions").Range("C" & i) <> "" And
ThisWorkbook.Sheets("Instructions").Range("F" & i) <> "" Then
TodaysDay = Format(ThisWorkbook.Sheets("Instructions").Range("C1"),
"ddd")
ReportDueDay = ThisWorkbook.Sheets("Instructions").Range("F" & i)
ReportType = ThisWorkbook.Sheets("Instructions").Range("C" & i)
If ReportDueDay = TodaysDay Then
MsgBox ("REMINDER ..... REMINDER ..... REMINDER " & vbNewLine &
ReportType & " due " & ReportDueDay)
End If
End If
Next i
End Sub

Any help appreciated!

Thanks,

Nick