MsgBox containing text from cell

[Nick Smith]

Folks,

Struggling with the MsgBox function.

I want a MsgBox to come up when it is the day a report is due. I have
defined the report title in one cell and the due day in another cell, eg
ThisWorkbook.Sheets("Instructions").Cell C29 contains "Status Report" and
ThisWorkbook.Sheets("Instructions").cell F29 contains "Wed". I want the
message to read "Reminder.... Project Status Report due today" with an OK
button to acknowledge when it is the day the report is due.
ThisWorkbook.Sheets("Instructions").Cell B1 contains the today() function to
define today's date.

However, if Cell C29 is empty, then no MsgBox should appear at all. That is
to say that the reminders are essentially user defined.

Was thinking along the lines of:

Private Sub ReminderMessageOnOpening()
If ThisWorkbook.Sheets("Instructions").Cells("C29") <> "" Then
If ThisWorkbook.Sheets("Instructions").Cells("F29") =
ThisWorkbook.Sheets
("Instructions").B1 Then
MsgBox("Reminder.... Project" &C29 "due" today)
End If
End If
End Sub

Can anyone help please as I am struggling with this one.

Thanks in advance,

Nick

[Stefi]

What does F29 really contains:
Text "Wed" or due date formatted like "ddd"?
A text never equals a real date value!
Where is the real due date?

If F29 contains real due date (formatted as date), then apply

If ThisWorkbook.Sheets("Instructions").Cells("F29") = Date Then

....

Regards,
Stefi

„Nick Smith” ezt �*rta:

> Folks,
>
> Struggling with the MsgBox function.
>
> I want a MsgBox to come up when it is the day a report is due. I have
> defined the report title in one cell and the due day in another cell, eg
> ThisWorkbook.Sheets("Instructions").Cell C29 contains "Status Report" and
> ThisWorkbook.Sheets("Instructions").cell F29 contains "Wed". I want the
> message to read "Reminder.... Project Status Report due today" with an OK
> button to acknowledge when it is the day the report is due.
> ThisWorkbook.Sheets("Instructions").Cell B1 contains the today() function to
> define today's date.
>
> However, if Cell C29 is empty, then no MsgBox should appear at all. That is
> to say that the reminders are essentially user defined.
>
> Was thinking along the lines of:
>
> Private Sub ReminderMessageOnOpening()
> If ThisWorkbook.Sheets("Instructions").Cells("C29") <> "" Then
> If ThisWorkbook.Sheets("Instructions").Cells("F29") =
> ThisWorkbook.Sheets
> ("Instructions").B1 Then
> MsgBox("Reminder.... Project" &C29 "due" today)
> End If
> End If
> End Sub
>
> Can anyone help please as I am struggling with this one.
>
> Thanks in advance,
>
> Nick
>

[Jim Rech]

Something like:

If Range("A1").Value = Format(Now, "ddd") Then
MsgBox "x"
End If

No need to refer to a cell with Today in it as VBA knows the current date.
You'd have to use "Thu" for Thurday but the rest are obvious.

--
Jim
"Nick Smith" <[email protected]> wrote in message
news:[email protected]...
| Folks,
|
| Struggling with the MsgBox function.
|
| I want a MsgBox to come up when it is the day a report is due. I have
| defined the report title in one cell and the due day in another cell, eg
| ThisWorkbook.Sheets("Instructions").Cell C29 contains "Status Report" and
| ThisWorkbook.Sheets("Instructions").cell F29 contains "Wed". I want the
| message to read "Reminder.... Project Status Report due today" with an OK
| button to acknowledge when it is the day the report is due.
| ThisWorkbook.Sheets("Instructions").Cell B1 contains the today() function
to
| define today's date.
|
| However, if Cell C29 is empty, then no MsgBox should appear at all. That
is
| to say that the reminders are essentially user defined.
|
| Was thinking along the lines of:
|
| Private Sub ReminderMessageOnOpening()
| If ThisWorkbook.Sheets("Instructions").Cells("C29") <> "" Then
| If ThisWorkbook.Sheets("Instructions").Cells("F29") =
| ThisWorkbook.Sheets
| ("Instructions").B1 Then
| MsgBox("Reminder.... Project" &C29 "due" today)
| End If
| End If
| End Sub
|
| Can anyone help please as I am struggling with this one.
|
| Thanks in advance,
|
| Nick
|

[Nick Smith]

Thanks Stefi. Helped, but getting a "Type Mismatch" error
Sub ReminderMessageOnOpening()

Dim ReportType As String

If ThisWorkbook.Sheets("Instructions").Cells("C29") <> "" Then
ReportType = ThisWorkbook.Sheets("Instructions").Cells("C29")
If ThisWorkbook.Sheets("Instructions").Cells("F29") = Date Then
MsgBox ("Reminder.... Project " & ReportType & "due today")
End If
End If
End Sub

Have I defined the ReportType variable incorrectly? Any ideas?

Thanks,

Nick

"Stefi" wrote:

> What does F29 really contains:
> Text "Wed" or due date formatted like "ddd"?
> A text never equals a real date value!
> Where is the real due date?
>
> If F29 contains real due date (formatted as date), then apply
>
> If ThisWorkbook.Sheets("Instructions").Cells("F29") = Date Then
>
> ...
>
> Regards,
> Stefi
>
> „Nick Smith” ezt �*rta:
>
> > Folks,
> >
> > Struggling with the MsgBox function.
> >
> > I want a MsgBox to come up when it is the day a report is due. I have
> > defined the report title in one cell and the due day in another cell, eg
> > ThisWorkbook.Sheets("Instructions").Cell C29 contains "Status Report" and
> > ThisWorkbook.Sheets("Instructions").cell F29 contains "Wed". I want the
> > message to read "Reminder.... Project Status Report due today" with an OK
> > button to acknowledge when it is the day the report is due.
> > ThisWorkbook.Sheets("Instructions").Cell B1 contains the today() function to
> > define today's date.
> >
> > However, if Cell C29 is empty, then no MsgBox should appear at all. That is
> > to say that the reminders are essentially user defined.
> >
> > Was thinking along the lines of:
> >
> > Private Sub ReminderMessageOnOpening()
> > If ThisWorkbook.Sheets("Instructions").Cells("C29") <> "" Then
> > If ThisWorkbook.Sheets("Instructions").Cells("F29") =
> > ThisWorkbook.Sheets
> > ("Instructions").B1 Then
> > MsgBox("Reminder.... Project" &C29 "due" today)
> > End If
> > End If
> > End Sub
> >
> > Can anyone help please as I am struggling with this one.
> >
> > Thanks in advance,
> >
> > Nick
> >

[Stefi]

Sorry, I should have noticed before, the correct reference to cell F29 in VBA
is Range("F29") or Cells(29,6). Check the format of F29, it should be Date!

> Sub ReminderMessageOnOpening()
>
> Dim ReportType As String
>
> If ThisWorkbook.Sheets("Instructions").Range("C29") <> "" Then
> ReportType = ThisWorkbook.Sheets("Instructions").Range("C29")
> If ThisWorkbook.Sheets("Instructions").Range("F29") = Date Then
> MsgBox ("Reminder.... Project " & ReportType & "due today")
> End If
> End If
> End Sub
>
Regards,
Stefi

[santosh]

Try the the colde below. You need to put it into ThisWorkbook. First time
for me on here


Private Sub Workbook_Open()
'Run this program only if C29 is not null
If Worksheets("Instructions").Range("C29").Value <> "" Then

' Get strDayOfWeek from the worksheet
Dim strDayOfWeek As String
strDayOfWeek = LCase(Trim(Worksheets("Instructions").Range("F29").Value))

' Weekday function returns 1 for Sunday, 2 for Monday, 3 for Tuesday etc
' Set integer intDay accordingly
Dim intDay As Integer
If (strDayOfWeek = "sunday") Then
intDay = 1
End If
If (strDayOfWeek = "monday") Then
intDay = 2
End If
If (strDayOfWeek = "tuesday") Then
intDay = 3
End If
If (strDayOfWeek = "wednesday") Then
intDay = 4
End If
If (strDayOfWeek = "thursday") Then
intDay = 5
End If
If (strDayOfWeek = "friday") Then
intDay = 6
End If
If (strDayOfWeek = "saturday") Then
intDay = 7
End If

' Display message accordingly
If intDay = Weekday(Now()) Then
MsgBox "Reminder.... Project " & Cells("C29").Value & " due " &
strDayOfWeek
End If
End If
End Sub







"Nick Smith" wrote:

> Folks,
>
> Struggling with the MsgBox function.
>
> I want a MsgBox to come up when it is the day a report is due. I have
> defined the report title in one cell and the due day in another cell, eg
> ThisWorkbook.Sheets("Instructions").Cell C29 contains "Status Report" and
> ThisWorkbook.Sheets("Instructions").cell F29 contains "Wed". I want the
> message to read "Reminder.... Project Status Report due today" with an OK
> button to acknowledge when it is the day the report is due.
> ThisWorkbook.Sheets("Instructions").Cell B1 contains the today() function to
> define today's date.
>
> However, if Cell C29 is empty, then no MsgBox should appear at all. That is
> to say that the reminders are essentially user defined.
>
> Was thinking along the lines of:
>
> Private Sub ReminderMessageOnOpening()
> If ThisWorkbook.Sheets("Instructions").Cells("C29") <> "" Then
> If ThisWorkbook.Sheets("Instructions").Cells("F29") =
> ThisWorkbook.Sheets
> ("Instructions").B1 Then
> MsgBox("Reminder.... Project" &C29 "due" today)
> End If
> End If
> End Sub
>
> Can anyone help please as I am struggling with this one.
>
> Thanks in advance,
>
> Nick
>