I have a TextBox in a UserForm. using the event _AfterUpdate(), I want to update the format to TimeValue, "hh:mm" using this code.
Private Sub TimeTextBox_AfterUpdate()
On Error GoTo ErrorHandler
With TimeTextBox
.Value = Format(TimeValue(.Value), "hh:mm")
ErrorHandler:
.Value = Format(TimeValue(Now), "hh:mm")
End With
End Sub
The problem: it will always fail, even if I enter 13:13 in the box. How do I solve this?
As @MatthewD commented, you're creating an infinite loop by updating the textbox inside the update event. Eventually VBA quits looping, so it's not quite infinite. You keep getting the current time because you don't Exit Sub prior to your ErrorHandler:. The code under your error handling label gets executed 100% of the time.
If you put Exit Sub on the line above ErrorHandler: then the code below will only get executed if there's an error.
However, I'll propose a different way.
Private mbEventsDisabled As Boolean
Private Sub TimeTextBox_AfterUpdate()
Dim dtTime As Date
'See if you can convert the text into a time
On Error Resume Next
dtTime = TimeValue(Me.TimeTextBox.Value)
On Error GoTo 0
'if you can't, the variable will be zero and you set
'it to the current time
If dtTime = 0 Then dtTime = Now
'To prevent recursive calling, see if you've disabled events
If Not mbEventsDisabled Then
'disable events so you can update the textbox
mbEventsDisabled = True
'now this line will trigger AfterUpdate again, but it won't
'execute this if block because of the variable
Me.TimeTextBox.Value = Format(dtTime, "hh:mm")
'now re-enable events
mbEventsDisabled = False
End If
End Sub
You can't disable events in a userform with Application.EnableEvents, so you have to do it yourself. I create a module level variable called mbEventsDisabled that will keep track of whether events are enabled (Module level variables are declared in the declarations section of the module, outside of and above any procedures). It's best to name this variable in the negative because a Boolean variable will be False by default and you want disabled=false unless you set it otherwise.
Instead of updating the textbox in the main code and in an error handler, I just only update it in one spot. It makes the code cleaner I think.
