Format String from TextBox to TimeValue in Excel VBA -

i have textbox in userform. using event _afterupdate(), want update format timevalue, "hh:mm" using code.

private sub timetextbox_afterupdate() on error goto errorhandler  timetextbox     .value = format(timevalue(.value), "hh:mm") errorhandler:     .value = format(timevalue(now), "hh:mm") end  end sub 

the problem: fail, if enter 13:13 in box. how solve this?

as @matthewd commented, you're creating infinite loop updating textbox inside update event. vba quits looping, it's not quite infinite. keep getting current time because don't exit sub prior errorhandler:. code under error handling label gets executed 100% of time.

if put exit sub on line above errorhandler: code below executed if there's error.

however, i'll propose different way.

private mbeventsdisabled boolean  private sub timetextbox_afterupdate()      dim dttime date      'see if can convert text time     on error resume next         dttime = timevalue(me.timetextbox.value)     on error goto 0      'if can't, variable 0 , set     'it current time     if dttime = 0 dttime =      'to prevent recursive calling, see if you've disabled events     if not mbeventsdisabled          'disable events can update textbox         mbeventsdisabled = true          'now line trigger afterupdate again, won't         'execute if block because of variable         me.timetextbox.value = format(dttime, "hh:mm")          'now re-enable events         mbeventsdisabled = false     end if  end sub 

you can't disable events in userform application.enableevents, have yourself. create module level variable called mbeventsdisabled keep track of whether events enabled (module level variables declared in declarations section of module, outside of , above procedures). it's best name variable in negative because boolean variable false default , want disabled=false unless set otherwise.

instead of updating textbox in main code , in error handler, update in 1 spot. makes code cleaner think.