VBA Userform textbox date and time (97-XP)

Topic

New Reply

I hope that this is a simple question. I have included sample subroutines so that others may benefit from the parts I have made work.

This is a simple time difference problem complicated by doing data entry on a Userform and wanting to see the calculated time difference as the numbers are changed. To calculate I have to convert the text to a number then back to text.

I have a Userform that I want to recalculate the difference between 2 dates and times anytime I make a change. I am using the textbox_change event to call a single procedure. I have made this work for numbers and dates by changing the text to values, then doing the math, and changing it back again.

It is not as bad as it sounds, but works on everthing but time values. Here is an example I moved to a separte module so I could test it using a simple principal = qty * price equation. It updates anytime either qty or price changes on the form, I copied it here to keep it simple.

Sub testPrinciple()
Dim txtQty, txtPrice, txtPrinciple
txtQty = 10 ‘ text because it is on a UserForm
txtPrice = 12.34
‘ I removed the .text from the line below to allow it to work as a stand alone subroutine
‘ txtPrinciple.Text = Format(Str(Val(Format(txtQty, “#,###”)) * Val(Format(txtPrice, “##.0000”))), “$#,###.0000”)
txtPrinciple = Format(Str(Val(Format(txtQty, “#,###”)) * Val(Format(txtPrice, “##.0000”))), “$#,###.0000”)
MsgBox txtPrinciple
End Sub

The difference between two dates works like this
Sub TestDateDiff()
Dim txtDaysIn, txtStartDate, txtEndDate
txtStartDate = #1/1/2001# ‘this would come from the UserForm
txtEndDate = #1/2/2001#
‘ txtDaysDiff.Text = Format(Str(Val(Format(txtEndDate, “dd/mm/yy”)) – Val(Format(txtStartDate, “dd/mm/yy”))), “#0”) ‘ Column AI
txtDaysDiff = Format(Str(Val(Format(txtEndDate, “dd/mm/yy”)) – Val(Format(txtStartDate, “dd/mm/yy”))), “#0”) ‘ Column AI
MsgBox txtDaysDiff
End Sub

But the time version does not work because the Value of the time difference below should be 1 hour and 10 minutes but Val() does not keep the decimal part and returns 0

Sub testtime()
Dim txtTimeDiff, txtStartTime, txtEndtime
txtStartTime = #10:36:53 AM#
txtStoptime = #11:46:53 AM#
txtTimeDiff = Format(Str( Val(Format(txtStopTime, “hh:mm”)) – Val(Format(txtStartTime, “hh:mm”)) ), “hh:mm”)
MsgBox txtTimeIn
End Sub

I am sure my problem is in using Val() on a time as it loses

Any and all help will be greatly appreciated.

Reply | Quote

Replies

You will always get 0 because txtTimeIn is not given a value . You should have used MsgBox txtTimeDiff.
Your expression seems overly complicated.
Try Format(CDate(txtStopTime) – CDate(txtStartTime), “hh:mm”)

Reply | Quote

This works in my sample subroutine, even when I DIM txtTimeDiff as a string.

But when I cut and paste the code back inside the UserForm I get a 13 error, type mismatch, as soon as I open the form.

Is there something else I should add?

Reply | Quote

IsDate
See my post.
Each character that you type, generages a change event, so the stuff in the textbox is not always a valid time.
That’s why the change event is ugly.

Reply | Quote

Jim, your post was very confusing, so I hope that I provide the answer that you want. Assuming that you have three textboxes on a userform with the default names, here is the code to compute the difference:

Option Explicit
'
Private Sub TextBox1_Change()
    If IsDate(TextBox1) And IsDate(TextBox2) Then _
        TextBox3 = Format(CDate(TextBox1) - CDate(TextBox2), "hh:mm:ss")
End Sub
'
Private Sub TextBox2_Change()
    TextBox1_Change
End Sub

Hopefully, this will help. Now if you will let the teacher in me rant, (1) always use option explicit, (2) never use variants, (3) avoid using the change event if at all possible. HTH –Sam

Reply | Quote

I get an error 13, type mismatch as soon as I open the Userform

txtTimeDiff = Format(CDate(txtEndTime) – CDate(txtStartTime), “hh:mm”)

I tried this and get the same error.

txtTimeDiff = Format(CDate(txtEndTime), “hh:mm”)

but not if I use this

txtTimeDiff = Format(txtEndTime, “hh:mm”)

Any ideas?

Reply | Quote

IsDate

Reply | Quote

OK, let’s file this under the heading of “I think I am smarter than the computer” I’m not. I assumed that because I typed in the times, and I can see them on the screen, they must be times, so I did not need the ISDATE. But I added the test on the two textboxes and it worked perfectly.

Thank you very much for your help. I have attached you sample since I went to the trouble of creating it. Maybe it will help someone else follow along.

On a side note, I see you pasted your code and kept the formatting. Is there a place I can go and read some tips on doing this. Obviously it did not keep what I typed in.

Reply | Quote

> they must be times
They are when you finish, but the change event is executing your routine each time that you type a single character, so initially the second textbox is empty, which is not a valid time.

>kept the formatting
Click on Help at the top, then Index, then Markup, look for the section on PRE. That will take you here.
In a nutshell, after you paste your code into a post, you place a

 before your code and a 

afterwards.

Reply | Quote