Insert spinning button with time format in Excel 2000?

[Pat Hughes]

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.

[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

"Pat Hughes" <[email protected]> wrote in message
news:[email protected]...
>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.


Pat Hughes wrote:
>
> 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

[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

[Pat Hughes]

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" wrote:

> 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.
>
>
> Pat Hughes wrote:
> >
> > 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
>

[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




Pat Hughes wrote:
>
> 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" wrote:
>
> > 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.
> >
> >
> > Pat Hughes wrote:
> > >
> > > 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
> >

--

Dave Peterson