Date format in a message box in Excel VBA

Question

I have found a way to display a message box based on a condition, however, the date format that appears is not what I would like to display or what is being displayed in my worksheet. Cells(i, 4).Value is the date value that I want to display as "mmmm dd, yyyy" but it displays as "m/d/yyyy". My actual Excel data has the date as "mmmm dd, yyyy".

I have tried multiple ways to format the date but I keep getting an error message. Is it possible to change the date format within the message box?

Sub Alert()
    Dim i As Long
    i = 3

    While Workbooks("Issuers.xlsm").Sheets("Summary of Covered Companies").Cells(i, 5) <> ""

    With Workbooks("Issuers.xlsm")

        If .Sheets("Summary of Covered Companies").Cells(i, 5).Value = 1 Then

             MsgBox Workbooks("Issuers.xlsm").Sheets("Summary of Covered Companies").Cells(i, 3).Value & " is issuing their next financial statement tomorrow (" & _
            Workbooks("Issuers.xlsm").Sheets("Summary of Covered Companies").Cells(i, 4).Value & ")."

        End If

    End With

   i = i + 1

   Wend

End Sub

Answer 1

The following should fix it

MsgBox Workbooks("Issuers.xlsm").Sheets("Summary of Covered Companies").Cells(i, 3).Value & " is issuing their next financial statement tomorrow (" & _
        Format(Workbooks("Issuers.xlsm").Sheets("Summary of Covered Companies").Cells(i, 4).Value,"[$-409]mmmm d, yyyy;@") & ")."

PS: Use macro recorder for getting most excel commands such as number formats

Answer 2

I think you can get what you want by using .text property intead of .value

Try this example:

Sub Alert() 

    Dim i       As  Long 
    Dim wb      As Workbook
    Dim ws      As Worksheet

    Dim strDisplayDate  As String
    Dim strCompany      As String

    i = 3

    Set wb = Workbooks("Issuers.xlsm")
    Set ws = wb.Sheets("Summary of Covered Companies")

    With ws
        While .Cells(i, 5) <> ""

            If .Cells(i, 5).Value = 1 Then

                strCompany = .Cells(i, 3).Value

                ' Use text not value to store what's displayed on screen            
                strDisplayDate = .Cells(i, 4).Text 

                strMessage = strCompany & " is issuing their next financial statement tomorrow (" & strDisplayDate & ")."
                MsgBox strMessage

            End If

            i = i + 1

        Wend
    End With

End Sub

If you really do want to format the value yourself, you can use .Value2 to get the underlying value like Format(.Cells(i,4,Value2),"mmmm dd, yyyy")