Insert spinning button with time format in Excel 2000?

[Guest]

I am making a form template in Excel 2000. Every time I try to enter a
spinner button I can't get it to work with time format. I want the time to
be between 00:00 and 24:00 with 15 minute increments. I've tried with and
without the colons but nothing is working.

 

[Dave Peterson]

I put a spinner from the Forms toolbar on a worksheet.
I rightclicked on it and chose format control.
On the control tab, I used:
Minimum value: 0
maximum value: 96
Incremental change: 1
And cell link A1
Then in B1, I put:
=a1/96
and formatted as time: hh:mm

96 is the number of 15 minute increments in a day (4*24).

You could use this same technique with a spinbutton from the control toolbox
toolbar.

 

[Biff]

Hi!

Use a helper cell.

Set the limits of the spinner to:

Minimum value = 0
Maximum value = 96
Incremental change = 1

Assume the linked cell is A1

Format the helper cell as [h]:mm

Enter this formula in the helper cell:

=A1*15/1440

Biff

 

[Cutter]

Have the spinner linked to a cell hidden by the spinner, I'll use A1 for
this example. Have your spinner set to min value = 0 and max value = 96
with 1 as the incremental change.

In the adjacent cell, where you want the time to show, type this
formula:

=A1*((1/24)*0.25)

Now custom format that cell as [h]:mm

 

[Guest]

Thanks Dave and Biff,
Is there a way to make a default value like 7:45 but be able to change it if
necessary? Is there a way to type in a number like 7 and have it
automatically go to 7:00? Would this work when I save as a template and then
when my users use it as a worksheet the formulas would be locked and they
could just type a 7 for it to go to 7:00?

Thanks so much for your help!!!
Pat

 

[Dave Peterson]

You could initialize the spinner to 31.

rightclick on the spinner
format control
control tab
Put 31 in the current value

But I'm not sure if that's enough to make it a default.

I guess you could use some routine that would put 31 into that linked cell --
but when would it get reset?

And where would you want to type that 7? In the linked cell?

You could use an event to convert the typed value, but I think I'd stick with
the spinner.

But if you want to try:

Rightclick on the worksheet tab with the linked cell.
select view code
paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myCell As Range
Set myCell = Me.Range("a1") 'that linked cell

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(myCell, Target) Is Nothing Then Exit Sub

On Error GoTo errHandler:

If IsNumeric(Target.Value) Then
If Int(Target.Value) = Target.Value Then
Application.EnableEvents = False
Target.Value = (Target.Value / 24) * 96
Target.NumberFormat = "General"
End If
End If

errHandler:
Application.EnableEvents = True

End Sub