Need help with MsgBox..

Hi, I need help in creating a message box that runs once a month on a specified date i.e. the last day of every month for example. Is it possible to do this..... thanks.

Hello Ahmed,

Do you need the message box to appear when the workbook is first opened? When you say last day do you mean the last workday of the month or last day like the 30th or 31st?

Thanks,
Leith Ross

Leith,
yea when the workbook is opened on that day. The last working day of that month..

thanks

Hello Ahmed,

I apologize for the delay. Here is the code to run a message box on the last working day of the month when the workbook is opened. Put this code in the Workbook Open event.


Private Sub Workbook_Open()

Dim Today
Dim YearNow
Dim MonthNow
Dim LastDay

Today = Format(Now(), "mmm-dd-yyyy")
YearNow = Format(Today, "yyyy")
MonthNow = Format(Today, "mmm")

Select Case MonthNow
Case Is = "Jan", "Mar", "May", "Jul", "Aug", "Oct", "Dec"
LastDay = Weekday(MonthNow & "-31-" & YearNow)
If LastDay = 7 Then LastDay = MonthNow & "-30-" & YearNow
If LastDay = 1 Then LastDay = MonthNow & "-29-" & YearNow
Case Is = "Feb"
LastDay = Weekday(MonthNow &"-28-" & YearNow)
If LastDay = 7 Then LastDay = MonthNow & "-27-" & YearNow
If LastDay = 1 Then LastDay = MonthNow & "-26-" & YearNow
Case Is = "Apr", "Jun", "Sep", "Nov"
LastDay = Weekday(MonthNow & "-30-" & YearNow)
If LastDay = 7 Then LastDay = MonthNow & "-29-" & YearNow
If LastDay = 1 Then LastDay = MonthNow & "-28-" & YearNow
End Select

If Today = LastDay Then
<Display your Message Box here>
End If

End Sub

If you have any questions, contact via e-mail at [email protected].

Sincerely,
Leith Ross