Msgbox Calculator

[sassriverrat]

Good Morning,

I've started some code below where I'd like the data from a few cells (times and dates) to be computed. The answer should popup in a msgbox and then it should ask me if this data should be put into Cells "W33" and "Y33"

I've got some of this code wrong and it's giving me errors on the path4. Thanks for the help.

Sub ETA_CALC1()

Dim Path1 As String
Dim Path2 As String
Dim Path3 As String
Dim Path4 As String
Dim Path5 As String
Dim Path6 As String
Dim Path7 As String
Dim Path8 As String




Path1 = ActiveSheet.Range("R28").Value
Path2 = ActiveSheet.Range("T28").Value
Path3 = ActiveSheet.Range("S29").Value
Path4 = Value
Path5 = Sheets("Developer").Range("G2").Value
Path6 = ActiveSheet.Range("C5").Value
Path7 = ActiveSheet.Range("F4").Value
Path8 = ActiveSheet.Range("D4").Value


Path4 = (Path3 / (((Path2 + Path1 + (Time(Path5, 0, 0))) - (Path7 + Path8 + (Time(Path6, 0, 0)))) * 24))




MsgBox ("Based on your desired Arrival Time and Date and your mileage input, your speed required to make your ETA is:" & vbCrLf & Path4)










End Sub

 

[sassriverrat]

You sir, are amazing! Thank you again!

 

[sassriverrat]

One more question- if I wanted to change this slightly so that it would do all that it has, but within the same “answer window,” a few lines after the statement/answer the window said, “would you like to use this ETA? And then clicking yes, it would insert answer into A1 (time) and A2 (date) and no would just close the window. Thanks

 

[KolGuyXcel]

Try
Dim resp As Boolean
resp = MsgBox("Based on your expected speed and your mileage input, your ETA, corrected for arrival local time, is " & CDate(Path4), vbYesNo)
If resp = vbYes Then
'Your code
End If

 

[sassriverrat]

Ok last question, promise!

The cdate piece works for the message box. However, for the cells, when writing my if then for the yes, how would I separate the cdate into a time and a date. Basically when I hit yes, I want the answer (cDate(path4)) to go in so that A1 is the time and cell A2 is the date. Not sure how to separate. Thank you!!! I have the if part- simple simple.

 

[KolGuyXcel]

If resp Then
Range("A1") = Format(CDate(Path4), "hh:mm:ss") ' Returns a 24 hour time format
Range("A2") = Format(CDate(Path4), "dd-mmm-yy") ' Check your system date settings to fix the date and month
End If

 

[sassriverrat]

So doing it that way will result in the answer going across either way....I tried your code but added If Resp = vbYes Then and it won't transfer across if clicking yes, which is what i want. If I click "no" I don't want it to go

 

[KolGuyXcel]

Oh! I got so caught up in the formatting part of the date and time, I missed the basic nature of the return value!

Modify the section of the code I suggested earlier as follows:

Dim resp As Integer
resp = MsgBox("Based on your expected speed and your mileage input, your ETA, corrected for arrival local time, is " & CDate(Path4), vbYesNo)

If resp = 6 Then
Range("A1") = Format(CDate(Path4), "hh:mm:ss") ' Returns a 24 hour time format
Range("A2") = Format(CDate(Path4), "dd-mmm-yy") ' Check your system date settings to fix the date and month
End If
End Sub

The Yes No Buttons in a MsgBox return 6 when clicked "Yes" and 7 when Clicked "No".

 

[sassriverrat]

Oh this is excellent!!! Thank you very much