Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Mar 2002
    Location
    All Over, CA, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA Userform textbox date and time (97-XP)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Userform textbox date and time (97-XP)

    You will always get 0 because txtTimeIn is not given a value <img src=/S/grin.gif border=0 alt=grin width=15 height=15>. You should have used MsgBox txtTimeDiff.
    Your expression seems overly complicated.
    Try Format(CDate(txtStopTime) - CDate(txtStartTime), "hh:mm")

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA Userform textbox date and time (97-XP)

    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:
    <pre>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</pre>


    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
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    Lounger
    Join Date
    Mar 2002
    Location
    All Over, CA, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Userform textbox date and time (97-XP)

    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?

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA Userform textbox date and time (97-XP)

    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.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    Lounger
    Join Date
    Mar 2002
    Location
    All Over, CA, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Userform textbox date and time (97-XP)

    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?

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA Userform textbox date and time (97-XP)

    IsDate
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    Lounger
    Join Date
    Mar 2002
    Location
    All Over, CA, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Userform textbox date and time (97-XP)

    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.
    Attached Files Attached Files

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA Userform textbox date and time (97-XP)

    > 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 <!t>[pre]<!/t> before your code and a <!t>[/pre]<!/t> afterwards.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •