SpinButton Incrementing time behaviour

C

Corey ....

The following code is suppose to increment time in hh:mm AM/PM.
I set the Starting time to a value of 930, which is 3:30 PM.

I change the Small Increment of the SpinButton to 15, so it only moves in
15min increments.
But the default value of the Texbox ALWAYS seem to come up as 8:00AM, even
though i have the 930 set to the spinbutton.

When i click the SpinButton_Up once it moves from 8:00AM to 3:45PM.

If i then click the SpinButton_Down ONCE it moves from the 3:45PM to 8:30PM,
then another click it moves to 3:15PM.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub CommandButton1_Click()
UserForm1.ActiveControl = TextBox1.Value
Unload Me
End Sub

~~~~~~~~~~
Private Sub SpinButton1_SpinDown()
TextBox1 = Format(TextBox1.Value, "00.00")
If TextBox1 = "" Then
TextBox1.Value = SpinButton1.Value
Else: TextBox1 = SpinButton1.Value / 1440 - CInt(TextBox1.Value)
End If
TextBox1 = Format(TextBox1.Value, "hh:mm AM/PM")
End Sub
~~~~~~~~~
Private Sub SpinButton1_SpinUp()
TextBox1 = Format(TextBox1.Value, "00.00")
If TextBox1 = "" Then
TextBox1.Value = SpinButton1.Value
Else: TextBox1 = SpinButton1.Value / 1440 + CInt(TextBox1.Value)
End If
TextBox1 = Format(TextBox1.Value, "hh:mm AM/PM")
End Sub
~~~~~~~~~
Private Sub UserForm_Initialize()
TextBox1.Value = Format(0.3333333, "hh:mm AM/PM")
SpinButton1.Value = 930
End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Am i missing something here ?


I also have another identical form with the same code and set the SpinButton
to 450 (7:30AM), but it ALWAYS shows 8:00AM instead ???

Why ?
 
D

Dave Peterson

You're doing a lot of time arithmetic in your logic and I think you're screwing
a bit of it up.

I'm not sure what you're doing with lines like:
TextBox1 = Format(TextBox1.Value, "00.00")

Have you thought of just adding/subtracting 15 minutes each time the spinner is
clicked?

Option Explicit
Private Sub CommandButton1_Click()
'UserForm1.ActiveControl = me.textbox1.Value
Unload Me
End Sub
Private Sub SpinButton1_SpinDown()
Dim myTime As Date

On Error Resume Next
myTime = CDate(Me.TextBox1.Value)
If Err.Number <> 0 Then
Beep
Me.Label1.Caption = "Enter a good time!"
Err.Clear
Else
Me.TextBox1 = Format(myTime - 15 / 24 / 60, "hh:mm AM/PM")
Me.Label1.Caption = ""
End If
On Error GoTo 0
End Sub
Private Sub SpinButton1_SpinUp()
Dim myTime As Date

On Error Resume Next
myTime = CDate(Me.TextBox1.Value)
If Err.Number <> 0 Then
Beep
Me.Label1.Caption = "Enter a good time!"
Err.Clear
Else
Me.TextBox1 = Format(myTime + 15 / 24 / 60, "hh:mm AM/PM")
End If
On Error GoTo 0
End Sub
Private Sub UserForm_Initialize()
Me.TextBox1.Value = "09:30 AM"
Me.Label1.Caption = ""
Me.TextBox1.Enabled = False 'maybe????
End Sub

Have you thought of changing the textbox to a label (if you don't want the users
typing in the time).

And if you the users to be able to type, but then clicking would get back to the
15 minute increment...

You could add a line like:
myTime = Application.Ceiling(myTime, TimeSerial(0, 15, 0))
before both of these lines:
Me.TextBox1 = Format(myTime - 15 / 24 / 60, "hh:mm AM/PM")
 
C

Corey ....

Dave,
I like your idea of the Label instead of a textbox.
I ended up with:

Private Sub CommandButton1_Click()
UserForm1.ActiveControl = Label4
Unload Me
End Sub

Private Sub SpinButton1_SpinDown()
Dim myTime As Date
On Error Resume Next
myTime = CDate(Me.Label4)
If Err.Number <> 0 Then
Beep
Err.Clear
Else
myTime = Application.Ceiling(myTime, TimeSerial(0, 15, 0))
Me.Label4 = Format(myTime - 15 / 24 / 60, "hh:mm AM/PM")
End If
On Error GoTo 0

End Sub

Private Sub SpinButton1_SpinUp()
Dim myTime As Date
On Error Resume Next
myTime = CDate(Me.Label4)
If Err.Number <> 0 Then
Beep
Err.Clear
Else
myTime = Application.Ceiling(myTime, TimeSerial(0, 15, 0))
Me.Label4 = Format(myTime + 15 / 24 / 60, "hh:mm AM/PM")
End If
On Error GoTo 0

End Sub

Private Sub UserForm_Initialize()
If UserForm1.ActiveControl <> "" Then
Me.Label4 = UserForm1.ActiveControl
Else: Me.Label4 = "03:30 PM"
Label4 = Format(Label4, "hh:mm AM/PM")
End If
End Sub



Corey....
 
D

Dave Peterson

If you're going to use a label, then you could dump all that error checking
stuff.

If the user can't break it (and you're careful with your code), it should always
be ok.
 
D

Dave Peterson

And if you're using a label, you don't have to worry about the
application.ceiling() line.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top