VBA Message Box to Update Today's Day in Microsoft Excel 

If you want to be automatically update today’s day of the week then this article is for you. This article focus on updating user the current day of the week via msgbox in vba.

Question): I want a code to determine the day of the week.

We need to follow the below steps to launch VB editor

• Click on Developer tab
• From Code group select Visual Basic

• Click on Insert then Module

• This will create new module
• Enter the following code in the Module

Sub TodaysDay()

 Select Case DatePart("w", Date, vbMonday)

    Case 1

        MsgBox "Mon"

    Case 2

        MsgBox "Tue"

    Case 3

        MsgBox "Wed"

    Case 4

        MsgBox "Thur"

    Case 5

        MsgBox "Fri"

    Case 6

        MsgBox "Sat"

    Case 7

        MsgBox "Sun"

    End Select

End Sub

Note: Today’s Date is June 5, 2015 i.e. Friday

“vbMonday” is used to return Monday as first day of the week; in case Sunday is the first day of the week then you need to use “vbSunday”

• Press ALT + F8 shortcut key to open Macro window
• Select the macro that you have just now created

• Click on Run & we will get the name of today’s date.

• If you want to change the format from Fri to Friday
• You need to do slight changes in the code

Sub TodaysDay()

 Select Case DatePart("w", Date, vbMonday)

    Case 1

        MsgBox "Monday"

    Case 2

        MsgBox "Tuesday"

    Case 3

        MsgBox "Wednesday"

    Case 4

        MsgBox "Thursday"

    Case 5

        MsgBox "Friday"

    Case 6

        MsgBox "Saturday"

    Case 7

        MsgBox "Sunday"

    End Select

End Sub

• This time you will be able to display full name of the day.

In this we can use VBA message box to notify user about the current day of the week.