Formatting of Time in message box

[Kentetsu]

Hi all,

So, I've got a message box that will pop up to tell the user what ingredient to add, and how long it will have to mix for. However, the time (which should be displayed as minutes, like 2:00) shows up as 8.333333333333E-2 (or something like that). I've checked the formatting of the cell that I am drawing the info from, and it appears correctly. Not sure how to change the formatting in a message box.

MsgBox "Your 2nd Ingredient(s) = " & Sheets("Scrap").Range("E15").Value _
    & " And Will Mix for " & Sheets("Scrap").Range("E16").Value & vbCrLf & vbCrLf & _
     "Add The Ingredient(s), THEN Click OK", vbOKOnly

Any advice is greatly appreciated...

 

[JackDanIce]

Try:

Sub M1()


    Dim strMsg      As String
    Dim strTime     As String
    
    strMsg = "Your 2nd Ingredient(s) = @2ING@1@1And will mix for: @TIME@1@1Add the ingredient(s), THEN Click ok"
    
    With Sheets("Scrap")
        strTime = Format(.Cells(16, 5).Value, "MM:SS")
        
        strMsg = Replace(strMsg, "@2ING", .Cells(15, 5).Value)
        strMsg = Replace(strMsg, "@TIME", strTime)
        
        MsgBox Replace(strMsg, "@1", vbCrLf), vbOKOnly, "Second Ingredients"
    End With
        
End Sub

 

[Kentetsu]

Thanks JackDanIce, that definitely gets me closer.

Now the time (2:00) is being displayed as 12:00. I checked another part number with a different mix time (10:00), and that also displays as 12:00. Any thoughts on that?

Thanks again...

 

[JackDanIce]

I'm guessing it may be a difference between the value within the cell vs how the cell has been formatted.

What is the actual value in E16, how is it displayed and what is the cell formatting set to?

 

[Kentetsu]

The formatting of the cell is h:mm and it displays as 2:00, 10:00, etc.

The cell contains a Vlookup that returns the mix time, so maybe that's the problem?

 

[JackDanIce]

If the cell formatting is h:mm, then 2:00 I believe is the conversion of the value in the cell displayed as hours and minutes so that's 2 hours of time rather than 2 minutes.

Try using either line in blue (not both in the same procedure):

Sub M1()


    Dim strMsg      As String
    Dim strTime     As String
    
    strMsg = "Your 2nd Ingredient(s) = @2ING@1@1And will mix for: @TIME@1@1Add the ingredient(s), THEN Click ok"
    
    With Sheets("Scrap")
        
        strTime = Format(.Cells(16, 5), "MM:SS")
        strTime = .Cells(16, 5).Text
        
        strMsg = Replace(strMsg, "@2ING", .Cells(15, 5).Value)
        strMsg = Replace(strMsg, "@TIME", strTime)
        
        MsgBox Replace(strMsg, "@1", vbCrLf), vbOKOnly, "Second Ingredients"
    End With
        
End Sub

I'm guessing you know how to comment code so can comment out each blue line in turn as you test the code...

 

[Kentetsu]

The second line did the trick. Thank you very much for your time and knowledge!

 

[JackDanIce]

You're welcome, glad it's working!

 

[Kentetsu]

Hm, to keep things simple I attempted to reuse strTime as the value for Application.wait But, I'm getting a type mismatch error when it hits Application.wait

strMsg = _
"Your 1st Ingredient(s) = @2ING@1@1And will mix for: @time Minutes.  @1@1 Add the ingredient(s), THEN Click OK to Begin Timing"

With Sheets("Scrap")
        strTime = .Cells(14, 5).Text
        strMsg = Replace(strMsg, "@2ING", .Cells(13, 5).Value)
        strMsg = Replace(strMsg,  @time", strTime)
        MsgBox Replace(strMsg, "@1", vbCrLf), vbOKOnly, "First Ingredient(s)"
    End With
    
Sheets("Stop").Visible = True
Sheets("Go").Visible = xlVeryHidden
    
Application.Wait (Now + TimeValue(strTime))

Sheets("Go").Visible = True
Sheets("Stop").Visible = xlVeryHidden

 

[JackDanIce]

I'm guessing because strTime is a string variable and TimeValue requires a time or at least numerical variable?