Str(Answer) Question

[bumper338]

Below is some programming I have put in an excel timesheet spreadsheet. What
I am wanting to do is to take the total hours add them up, and if over 40 a
message box that say you have X number of extra hours. I can get the
Str(answer) to appear in the message box but it returns a value of 0 instead
of the true number. What am I missing?

If Range("P28").Value > 40 And Range("P19").Value = "" And
Range("Q19").Value = "" Then
Answer = MsgBox("You have" & Str(Answer) & "extra hours, Do you want
to split these hours between Overtime and Comp Time?", vbYesNo + vbQuestion,
"Week 1 Split")

If Answer = vbYes Then
strInput = InputBox("How much time as Overtime?", "OT")
Range("P19").Value = strInput
strInput = InputBox("How much time as Comp Time?", "Comp")
Range("Q19").Value = strInput

Else

If Answer = vbNo Then
answer1 = MsgBox("You've elected NOT to split your hours
between OT and Comp.", vbOK, "Monday Confirm")

If answer1 = vbOK Then
answer2 = MsgBox("Do you want the time as OT (click
Yes) or Comp (click No)?", vbYesNo, "Monday OT/Comp2")

If answer2 = vbYes Then
Range("P19").Formula = Formula1

Else

If answer2 = vbNo Then
Range("Q19").Formula = Formula1

End If
End If
End If
End If
End If
End If

 

[JMB]

I don't see any code prior to this line that assigns a value to "answer". As
far as I can tell, at this point "answer" has a value of 0.

Answer = MsgBox("You have" & Str(Answer) & "extra hours, Do you want
to split these hours between Overtime and Comp Time?", vbYesNo + vbQuestion,
"Week 1 Split")

You could try:
Answer = MsgBox("You have " & Format(Range("P28").Value - 40, "0.00") & "
extra hours, Do you want to split these hours between Overtime and Comp
Time?", vbYesNo + vbQuestion, "Week 1 Split")

 

[bumper338]

That works great. Thanks,

I don't see any code prior to this line that assigns a value to "answer". As
far as I can tell, at this point "answer" has a value of 0.


You could try:
Answer = MsgBox("You have " & Format(Range("P28").Value - 40, "0.00") & "
extra hours, Do you want to split these hours between Overtime and Comp
Time?", vbYesNo + vbQuestion, "Week 1 Split")

 

[JMB]

Glad that helped. Thanks for the feedback.

That works great. Thanks,