• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

I need help with a VBA time function, please?

I have a userform for staff to enter a time value. I want to validate that the time value is in the format of "h:mm am/pm" and if not, I want an error message to show telling them to correct it. Then, I want the active userform field to return to the time field. My current code allows anything to be entered into the field and does not show the error message, nor does it require the field to be corrected. I'm sure it is something minor but I can't figure out what I'm missing. I hope someone here can help me! (As you can see, I have the value moving to my database field in a 'military' format at the end of my code which is what I want there.)


Code:
Private Sub txtDepartTime_AfterUpdate()
'When time is entered, time transfers immediately to spreadsheet datafield.

Dim TargetRow As Long
Dim TestTime As Date

On Error Resume Next
TestTime = TimeValue(txtDepartTime)
On Error GoTo 0

If TestTime = 0 Then
  MsgBox "Time entered is not valid.  Please enter time as hh:mm am/pm.", vbExclamation
  Exit Sub
End If

TargetRow = Sheets("Codes").Range("D43").Value + 1

With Sheets("Travel Expense Voucher").Range("Data_Start").Offset(TargetRow, 25)
.Value = TimeValue(txtDepartTime)
.NumberFormat = "hh:mm" 'departure time

End With

End Sub
 
Last edited:
Thank you, Marc L! Now we are getting somewhere. :) This gave me the error correctly, however, after the error, the active field (my cursor) still goes to the next field. How do I get it to return to the field in error (txtDepartTime)? My new code is below:


Code:
Private Sub txtDepartTime_AfterUpdate()
'When time is entered, time transfers immediately to spreadsheet time calculations datafield.

Dim TargetRow As Long
 
If Not txtDepartTime Like "#:## [ap]m" Then
  MsgBox "Time entered is not valid.  Please enter time as hh:mm am/pm.", vbExclamation
  Exit Sub
End If

TargetRow = Sheets("Codes").Range("D43").Value + 1

With Sheets("Travel Expense Voucher").Range("Data_Start").Offset(TargetRow, 25)
 .Value = TimeValue(txtDepartTime)
 .NumberFormat = "hh:mm" 'departure time
 
 End With
 
End Sub
 
Last edited:
That doesn't seem to be working either. :( This section of code is bugging me. LOL I have tried several different things, as you will see when I post my code again. I have commented out several things that I have tried but none of them seem to be working. Please know that I am still new to this and am learning as I go. Can you see what I have going that is causing the 'setfocus' code to not work? The only thing that I am missing is moving the cursor to the txtDepartTime field after the msgbox is presented and OK is selected by the user.

Code:
Private Sub txtDepartTime_AfterUpdate()
'When time is entered, time transfers immediately to spreadsheet time calculations datafield.

Dim TargetRow As Long

If Not txtDepartTime Like "#:## [ap]m" Then
  MsgBox "Time entered is not valid.  Please enter time as hh:mm am/pm.", vbExclamation
'If Response = vbOK Then
  txtDepartTime.SetFocus
  Exit Sub
End If
'End If


With Sheets("Travel Expense Voucher").Range("Data_Start").Offset(TargetRow, 25)
 .Value = TimeValue(txtDepartTime)
 .NumberFormat = "hh:mm" 'departure time
 
 End With
 
End Sub
 
Try tweaking the code a little and putting it into a different event handler:
Code:
Private Sub txtDepartTime_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'When time is entered, time transfers immediately to spreadsheet time calculations datafield.
Dim TargetRow As Long

If Not txtDepartTime Like "#:## [ap]m" Then
  MsgBox "Time entered is not valid.  Please enter time as hh:mm am/pm.", vbExclamation
  Cancel = True
  Exit Sub
End If

With Sheets("Travel Expense Voucher").Range("Data_Start").Offset(TargetRow, 25)
  .Value = TimeValue(txtDepartTime)
  .NumberFormat = "hh:mm"    'departure time
End With
End Sub
although this will insist that the user gets it right before being allowed to leave the textbox!

One possible problem with updating the sheet as soon as the user get's each textbox right in the userform is that should the user decide he's gone down the wrong track and wants to abandon the process (closes the userform), you're left with partial data on your worksheet. It's more conventional to allow the user to complete all the necessary controls on the userform, then get him to click an OK/Submit button, and the event handler of that button will then check all the user's entries and if there are any mistakes, bring the focus back to individual controls for re-entry/correction; setfocus should work properly in those circumstances. Once all the entries on the userform are validated, only then write the lot to the sheet.
 
Last edited:
Now it works correctly. :) Can you tell me, though. You had me change it to 'Exit', does that mean exit the field? I had the fields going to the spreadsheet immediately because checkboxes on the userform are controlled by calculations on the spreadsheet... these are vital to the process.... but I see that things are still working properly so 'exit' must not mean exit the form but the field? Either way... this works perfectly!!! Thank you so much for your help!! :)
 
Is it appropriate to ask one other question with regards to this? Or do I need to start a new thread? I'm trying to figure out if there is a way to allow users to enter their time as 9:00 am rather than having to use 09:00 am. If I change the coding to #:## [ap]m then users can't enter 12:00 am because it errors. Can I get help with this?
 
Last edited:
Back
Top