spinButton problem

Lectric · Apr 6th 2016

Can anyone shed some light on what I might be doing wrong. I have a user form with several fields and I'm trying to use spin buttons where possible to alleviate too much validation. With the code posted below the Date SpinButton works fine but I can't get either of the Time ones to work.

Code

Private Sub SpinButton1_Change()
txtWhen.Value = Format(Date + SpinButton1.Value, "dd-mmm-yy")
End Sub


Private Sub SpinButton2_Change()
txtStart.Value = Format(Time + SpinButton2.Value, "hh:mm")
End Sub


Private Sub SpinButton3_Change()
txtFinish.Value = Format(Time + SpinButton3.Value, "hh:mm")
End Sub


Private Sub UserForm_Initialize()
txtWhen.Value = Format(Date, "dd-mmm-yy")
txtStart.Value = Format(Time, "hh:mm")
txtFinish.Value = Format(Time, "hh:mm")
End Sub

Display More

Grimes0332 · Apr 6th 2016

Re: spinButton problem

Time in Excel is the decimal portion of 1 day. Midday is 0.5, 6:00pm is 0.75 etc. So you cannot simply add the spinbutton value, you need to divide it by the number of seconds in a day. (86400) If you want the spinbutton to increment seconds, minutes in a day if incrementing in minutes, same for hours.

You also have another problem in that the time constantly changes so you cannot use the current time in your calculations. You need to determine a fixed time, for example when the userform is loaded, and base the calculations on that.

Lectric · Apr 7th 2016

Re: spinButton problem

Problem solved.
Found the following code on MrExcel

Code

Private Sub SpinButton1_SpinDown()
If TextBox1 = vbNullString Then
    TextBox1 = "00:00"
Else
    TextBox1.Value = Format(DateAdd("n", -1, TextBox1.Value), "hh:mm")
End If
End Sub




Private Sub SpinButton1_SpinUp()
If TextBox1 = vbNullString Then
    TextBox1 = "00:00"
Else
    TextBox1.Value = Format(DateAdd("n", 1, TextBox1.Value), "hh:mm")
End If
End Sub

Display More

spinButton problem

Participate now!

Share

Users Viewing This Thread