Userform Textbox Time Error13

0

G'day, Excel Users; Been bogged down with a Userform Textbox calculating and changing time to minutes. I had tried various methods and formats. Although I'm quite comfortable with Userform, I have never tried to use time or convert time to minutes. It seems to me the issue here is with Textbox4', I can't seem to get around convert it to minutes, once I try to multiply the testbox4 by 1440 I get 'error 13'. With much appreciation if I can get some assistance.

Answer
Discuss

Answers

0
Selected Answer

Please check out the answer I have provided in this thread. I think it will solve your problem.

Edit 05 Dec 2019  ====================================

Your suggested solution is faulty.

Private Sub FaultySolution()
    Me.textbox1.Value = Format(Me.textbox1.Text, "00:00")
    Me.textbox2.Value = Format(Me.textbox2.Text, "00:00")
    Me.textbox3.Value = Format(TimeValue(Me.textbox2.Text) - TimeValue(Me.textbox1.Text), "hh:mm")
    Me.textbox4.Text = Me.textbox3.Text
    Me.textbox4.Text = TimeValue(Me.textbox3.Text) * (1440)
End Sub

Forgive me if I'm wrong but a Textbox's .Text property returns a string while its .Value property returns a variant of string type. The difference is miniscule.

VBA is able to coerce some numeric strings into numbers and actually use them in calculations. This process may be assisted if you use the .Value property. Or it might make no difference. Using the .Text property in calculations should offend your sense of logic, and rightly so.

My point is to explain my surprise at that your solution actually might work. It's not supposed to.

Format([Number], [Format As String]) requires a numeric value as first argument. The function returns a string.
Me.textbox1.Value = Format(Me.textbox1.Text, "00:00") therefore assigns a string (from TextBox1.Text) as the numeric argument of the Format function. It then assigns the string it creates to the TextBox's .Value property. VBA will assign a string version of that to the .Text property. The surprise is that the Format function is able to coerce the string in TextBox1.Text to a number. Beware of misinterpretations: A string like "8:30" may be misinterpreted as just 8, truncating the numbers following the string character ":".

Of course, the format "00:00" isn't a time format. The correct expression is "hh:mm".

I recommend the following two functions for your use. Paste them in your LogbktempDB form's code module and call them from there.

Private Function TextToHours(ByVal Txt As String) As Double
    TextToHours = TimeValue(Txt)
End Function

Private Function HoursToText(ByVal TimeDecimal As Double) As String
    HoursToText = Format(TimeDecimal, "hh:mm")
End Function

This would lead you to the solution below.

Private Sub ImprovedSolution()
    Me.textbox1.Value = HoursToText(TextToHours(Me.textbox1.Text))
    Me.textbox2.Value = HoursToText(TextToHours(Me.textbox2.Text))
    Me.textbox3.Value = HoursToText(TextToHours(Me.textbox2.Text) - TextToHours(Me.textbox1.Text))
    Me.textbox4.Text = Me.textbox3.Text
    Me.textbox4.Text = Me.textbox3.Text   ' not sure what you want here
End Sub

The essence of the two functions is that they take a string (from a textbox, like "8:30") and convert it to a number (in this example 0.354166666666667) or take a number (calculated from a string and manipulated) and convert it to a string for display in a textbox. Pay attention to the data types of the arguments and return values.

Yet, the above data manipulation makes no sense (to me). Me.textbox1.Value = HoursToText(TextToHours(Me.textbox1.Text)) will take the string value of the textbox, convert it to a number and convert that number back to a string, meaning the display doesn't change. I can't discern why you would want to change the display.

Me.textbox3.Value = HoursToText(TextToHours(Me.textbox2.Text) - TextToHours(Me.textbox1.Text)) is a better illustration of the use of my functions. The strings from Tbx2 and Tbx1 are converted to numbers, subtracted from each other creating a numeric result which the outer function converts to a text string for display in the box.

Discuss

Discussion

Solved: Finally I solved this issue with textbox 4 13 error, it was looking at me all the time, I was convinced that the answer was otherwise for textbox4.
Here below is ithe outcome:- 
    Me.textbox1.Value = Format(Me.textbox1.Text, "00:00")     Me.textbox2.Value = Format(Me.textbox2.Text, "00:00")     Me.textbox3.Value = Format(TimeValue(Me.textbox2.Text) - TimeValue(Me.textbox1.Text), "hh:mm")     Me.textbox4.Text = Me.textbox3.Text     Me.textbox4.Text = TimeValue(Me.textbox3.Text) * (1440)

Thank you Kindly
jdgrapes (rep: 8) Dec 4, '19 at 8:10 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login