On my spreadsheet, cell "A1" shows the time of "9:50 PM".
However, when I run the following code:

MsgBox Range("A1").Value

the message box shows a time of "0.9097222222".

How do i fix this so it shows "9:50 PM" in the MessageBox?

Thank you!

Hi Robert,

Am Mon, 29 Apr 2013 12:28:25 -0700 schrieb Robert Crandal:

On my spreadsheet, cell "A1" shows the time of "9:50 PM".
However, when I run the following code:

MsgBox Range("A1").Value

the message box shows a time of "0.9097222222".

How do i fix this so it shows "9:50 PM" in the MessageBox?

try:
MsgBox Format([A1], "h:mm AM/PM")


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Robert,

Am Mon, 29 Apr 2013 21:32:45 +0200 schrieb Claus Busch:

MsgBox Format([A1], "h:mm AM/PM")

or:
MsgBox [A1].Text


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

"Claus Busch" wrote in

try:
MsgBox Format([A1], "h:mm AM/PM")


Thanks Claus. That code above did work great.

However, I found code in my project that does NOT
use Range("A1"). For example, a lot of my code
looks like this:

Sheets(i).Cells(n, 22).Value ' has a time value

I tried changing this code to:

Sheets(i).Cells(n, 22).Text

.....but that didn't work. What do I need to do with
this code that uses Sheets() rather than Range()??

Hi Robert,

Am Mon, 29 Apr 2013 12:48:02 -0700 schrieb Robert Crandal:

Sheets(i).Cells(n, 22).Value ' has a time value

I tried changing this code to:

Sheets(i).Cells(n, 22).Text

what do you want to do?
For me MsgBox Sheets(i).Cells(n, 22).Text
will work


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Robert,

Am Mon, 29 Apr 2013 21:56:12 +0200 schrieb Claus Busch:

what do you want to do?
For me MsgBox Sheets(i).Cells(n, 22).Text
will work

if you want to insert a time in sheets(i).cells(n,22) then try:
Sheets(i).Cells(n, 22) = Format(TimeValue("09:15:00"), "h:mm AM/PM")


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

"Claus Busch" wrote in message
...

what do you want to do?
For me MsgBox Sheets(i).Cells(n, 22).Text
will work


Now I know what went wrong. The cell that holds
a time value is protected as "hidden". Ugh, does that
mean I need to write code that temporarily removes
the "hidden" property?

Hi Robert,

Am Mon, 29 Apr 2013 13:04:47 -0700 schrieb Robert Crandal:

"Claus Busch" wrote in message
...

what do you want to do?
For me MsgBox Sheets(i).Cells(n, 22).Text
will work


Now I know what went wrong. The cell that holds
a time value is protected as "hidden". Ugh, does that
mean I need to write code that temporarily removes
the "hidden" property?


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Robert,

Am Mon, 29 Apr 2013 13:04:47 -0700 schrieb Robert Crandal:

Now I know what went wrong. The cell that holds
a time value is protected as "hidden". Ugh, does that
mean I need to write code that temporarily removes
the "hidden" property?

only the formula is hidden. The value or the text you can show with a
MsgBox


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2