Time text box with a spin button

[adam]

Hi Anyone,


How do I get a spinner to increment time which is in a text box "txttime" in a userform in Microsoft Excel 2007?

I want the spin button to increase the value, when the up arrow is clicked; to decrease the value, when the down arrow is clicked. Typically, the user can also type a text value directly into the associated text box.

Can the following code be modified accordingly in replacement of percentage to time.

Thanks in advance.

Code: Select all

Private Sub SpinButton1_SpinDown()
txttime.Value = Format(0, "0.0")
txttime.Value = txttime.Value + SpinButton1.Value / 100
txttime.Value = Format(txttime.Value, "0.0%")
End Sub

Private Sub SpinButton1_SpinUp()
txttime.Value = Format(0, "0.0")
txttime.Value = txttime.Value + SpinButton1.Value / 100
txttime.Value = Format(txttime.Value, "0.0%")
End Sub

[HansV]

Use code like this:

Code: Select all

Private Sub SpinButton1_SpinDown()
  Dim t As Date
  t = DateAdd("n", -1, TimeValue(Me.txtTime))
  Me.txtTime = Format(t, "hh:nn")
End Sub

Private Sub SpinButton1_SpinUp()
  Dim t As Date
  t = DateAdd("n", 1, TimeValue(Me.txtTime))
  Me.txtTime = Format(t, "hh:nn")
End Sub

Private Sub UserForm_Initialize()
  Me.txtTime = Format(0, "hh:nn")
End Sub

[adam]

Thanks for the help. Your code works the way as I had requested.