Userform code error?

cob98tp

Board Regular
Joined
Nov 18, 2004
Messages
146
Hi all,

I'm having a bit of problem with the code below. It is part of a user form which is used to input a start and end time for an event. It all seems to work fine EXCEPT when the time I input is 12:00. 11:45 and 12:15 both work ok, so I have no idea what is wrong!

Code:
Private Sub StartTime_Change()
StartTime = Format(StartTime, "hh:mm")
If StartTime > EndTime Then
    EndTime = StartTime.Value
End If

End Sub

The line that fails is the 'Format' line - it appears not to like the value of 0.5 (12:00) - any ideas why?

Thanks again!
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
What is “StartTime”? Is it a TextBox, ComboBox, etc? Where does it get it’s value from and how is that value changed?
 
Upvote 0
It is a combobox, the source for which is a list of times from 8:00 through to 7:45 in 15 minute intervals.

I have just been through and checked every interval, and 6:00 also causes the same error. Otherwise all other intervals work as intended.
 
Upvote 0
I think the problem may be that the procedure is being called by the change it makes. Try diableing it like this:

Code:
Dim Disabled As Boolean

Private Sub StartTime_Change()
    If Disabled = True Then
        Disabled = False
        Exit Sub
    Else
        Disabled = True
        StartTime = Format(Val(StartTime), "hh:mm")
    End If
End Sub
 
Upvote 0
I think the problem may be that the procedure is being called by the change it makes. Try diableing it like this:

Code:
Dim Disabled As Boolean

Private Sub StartTime_Change()
    If Disabled = True Then
        Disabled = False
        Exit Sub
    Else
        Disabled = True
        StartTime = Format(Val(StartTime), "hh:mm")
    End If
End Sub

That certainly seems to do the trick! I think that the reason that the recursive procedure caused this slightly bizarre error is that it was reading the value of 0.5 as 00:50 when it re-fired, thus converting twelve noon into half past midnight.
 
Upvote 0
Andrew, if I use your code suggestion, it removes the functionality I have tried to implement.

Basically I have 2 comboBoxes - StartTime and EndTime. Each populated from the same list of times at 15 minute intervals.

What I wanted was to have the value in EndTime change to = the input value in StartTime, if this time is later in the day. I.E. if EndTime = 8:00, and you input 11:00 in StartTime, I wanted EndTime to also equal 11:00, so that the change is then made easier.

And Vice-versa with EndTime.

The code I had was:

Code:
Private Sub EndTime_Change()
EndTime = Format(EndTime, "hh:mm")
If EndTime < StartTime Then
    StartTime = EndTime.Value
End If

End Sub

and

Code:
Private Sub StartTime_Change()
StartTime = Format(Val(StartTime), "hh:mm")
If StartTime > EndTime Then
    EndTime = StartTime.Value
End If

End Sub

Which works perfectly, except when the time selected in the combobox is 12:00 or 6:00 or 0.5 and 0.25 - strangely 0.75 (18:00) works fine as well. Any ideas why just these times would cause a problem?
 
Upvote 0
Does this help?
Code:
Dim Disabled As Boolean

Private Sub StartTime_Change()
If Disabled = True Then
    Disabled = False
    Exit Sub
Else
    Disabled = True
    StartTime = Format(Val(StartTime), "hh:mm")
End If
If StartTime > EndTime Then
    EndTime = StartTime.Value
End If
End Sub
 
Upvote 0
Yes, that works now Thank You!

.... except now if I scroll through the times in the comboBox using the up arrow key - every other time is not formatted as "hh:mm" - I just get the decimal value!

It works if I scroll using the down arrow though!

D'oh!
 
Upvote 0
Hey, Do you know what... I've got it to work using my original code, however I just added Val() to the format line, which was missing from before...

Works...
Code:
Private Sub StartTime_Change() 
StartTime = Format(Val(StartTime), "hh:mm") 
If StartTime > EndTime Then 
    EndTime = StartTime.Value 
End If 

End Sub

Doesn't work...
Code:
Private Sub StartTime_Change() 
StartTime = Format(StartTime, "hh:mm") 
If StartTime > EndTime Then 
    EndTime = StartTime.Value 
End If 

End Sub

Thanks for your help guys... Much appreciated!
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top