macro not running on workbook open
 

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

macro not running on workbook open
 

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


"Nick Smith" wrote in message
...
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

macro not running on workbook open
 

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

macro not running on workbook open
 

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

" wrote:

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

macro not running on workbook open
 

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

"NickHK" wrote:

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


"Nick Smith" wrote in message
...
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