Value Returned in Message Box Q

[Sean]

I have the following Message box that appears on Wroksheet_Open. It
returns a date value from sheet Sum AC1, but instead of appearing in
the MB as 02/12/07, it appears as 02/12/07 17:00. The cell AC1 is
formatted as Date: dd/mm/yy. How can I also get the value that is
returned in AC1 appear in the MB as dd/mm/yy?



MsgBox "Be on Time submitting" & vbCr & _
"" & vbCr & _
"Next due date is 5:00pm on " & Worksheets("Sum").Range("AC1")

 

[Gary Keramidas]

try using format

MsgBox Format(Worksheets("Sum").Range("AC1"), "mm/dd/yy")

 

[Chip Pearson]

Sean,

Change

Worksheets("Sum").Range("AC1")

to

Format(Worksheets("Sum").Range("AC1").Value,"dd-mmm-yyyy")

or to

Worksheets("Sum").Range("AC1").Text

Change the number format to the appropriate date string.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Sean]

Thanks Chip & Gary