MrExcel Publishing
Your One Stop for Excel Tips & Solutions

SpinBox -> TextBox Time


Posted by Malc on May 22, 2001 8:18 PM

I have a user form with a textbox on it where the user is rewquire to enter the time which subsequently gets posted to a cell.
I want the format of the textbox to limit the users input to a valid time format i.e. 9:53 am or 1:31 pm.
In order for the correct time to go into the cell.

I thought of using a spinbox to increment minutes and increase/decrease hours like setting an alarm clock but haven't fiqured out the right process.

Any Ideas?

Also how do I make the cursor flash in a selected textbox or combo box?


Posted by Dave Hawley on May 22, 2001 8:38 PM


Hi Malc

Try this code


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


To place the cursor in a textbox etc use:

Textbox1.Setfocus


Dave
OzGrid Business Applications

Posted by Dave Hawley on May 22, 2001 9:10 PM

Forgot to mention

...Set the Spinbutton Min to -32460 and the Max to 34560


Dave
OzGrid Business Applications

Posted by Malc on May 22, 2001 10:04 PM

Very Cunning - How

How do I preset the start time to 8:00 am and get the spinbox value to know what the start time is?

Posted by Dave Hawley on May 22, 2001 10:20 PM

Re: Very Cunning - How


Malc, you can do this on loading of your form:

Private Sub UserForm_Initialize()
TextBox1.Value = Format(0.3333333, "hh:mm AM/PM")
SpinButton1.Value = 480
End Sub

Dave

  • OzGrid Business Applications