MsgBox to display [hh]:mm

  • Thread starter Thread starter learner
  • Start date Start date
L

learner

Trying to finish up the macro with this message box

wtime=ActiveCell.Value
MsgBox("You worked for " & wtime & "Hrs:Min")

Cell where the value is taken from contains formula, is formatted as [hh]:mm
and is displayed right. But when I try to use the value in the message box, I
get something like 9.12435E-3.

It's gotta be simple but I can't figure it out.
Thank you
 
Try this

wtime = ActiveCell.Value
MsgBox ("You worked for " & Format(wtime, "hh:mm") & " Hrs:Min")

The cell's format isn't part of the value you are storing in wtime, so you
have to format it in the messagebox also.

Hope this helps,

Hutch
 
Hi

You have to format the timevalue:

wtime = ActiveCell.Value
MsgBox ("You worked for " & Format(wtime, "hh:mm") & " Hrs:Min")

Regards,
Per
 
Hi,

Try this

hrs = WorksheetFunction.Text(ActiveCell.Value, "[hh]") & " Hours "
Mins = Minute(ActiveCell.Value) & " Mins"

MsgBox "You worked for " & hrs & Mins

Mike
 
Thanks Tom, works perfect.

Tom Hutchins said:
Try this

wtime = ActiveCell.Value
MsgBox ("You worked for " & Format(wtime, "hh:mm") & " Hrs:Min")

The cell's format isn't part of the value you are storing in wtime, so you
have to format it in the messagebox also.

Hope this helps,

Hutch

learner said:
Trying to finish up the macro with this message box

wtime=ActiveCell.Value
MsgBox("You worked for " & wtime & "Hrs:Min")

Cell where the value is taken from contains formula, is formatted as [hh]:mm
and is displayed right. But when I try to use the value in the message box, I
get something like 9.12435E-3.

It's gotta be simple but I can't figure it out.
Thank you
Click to expand...
Click to expand...
 
I appreciate Mike,

It worked too and it now looks better than what I had in mind.

Mike H said:
Hi,

Try this

hrs = WorksheetFunction.Text(ActiveCell.Value, "[hh]") & " Hours "
Mins = Minute(ActiveCell.Value) & " Mins"

MsgBox "You worked for " & hrs & Mins

Mike

learner said:
Trying to finish up the macro with this message box

wtime=ActiveCell.Value
MsgBox("You worked for " & wtime & "Hrs:Min")

Cell where the value is taken from contains formula, is formatted as [hh]:mm
and is displayed right. But when I try to use the value in the message box, I
get something like 9.12435E-3.

It's gotta be simple but I can't figure it out.
Thank you
Click to expand...
Click to expand...
 
You will also want to use Mike's WorksheetFunction.Text method instead of
the Format function that others posted **if** there is any chance that the
total hours could be more than 24... the Format function does not support
the [hh] argument (that is, you can't use the square brackets with it) and
will "wrap" the hours back to 0 at the 24-hour mark.

--
Rick (MVP - Excel)


learner said:
I appreciate Mike,

It worked too and it now looks better than what I had in mind.

Mike H said:
Hi,

Try this

hrs = WorksheetFunction.Text(ActiveCell.Value, "[hh]") & " Hours "
Mins = Minute(ActiveCell.Value) & " Mins"

MsgBox "You worked for " & hrs & Mins

Mike

learner said:
Trying to finish up the macro with this message box

wtime=ActiveCell.Value
MsgBox("You worked for " & wtime & "Hrs:Min")

Cell where the value is taken from contains formula, is formatted as
[hh]:mm
and is displayed right. But when I try to use the value in the message
box, I
get something like 9.12435E-3.

It's gotta be simple but I can't figure it out.
Thank you
Click to expand...
Click to expand...
Click to expand...
 
Back
Top