Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Insert spinning button with time format in Excel 2000?
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. |
#2
|
|||
|
|||
Insert spinning button with time format in Excel 2000?
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 |
#3
|
|||
|
|||
Insert spinning button with time format in Excel 2000?
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" wrote in message ... 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. |
#4
|
|||
|
|||
Insert spinning button with time format in Excel 2000?
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 -- Cutter ------------------------------------------------------------------------ Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848 View this thread: http://www.excelforum.com/showthread...hreadid=479580 |
#5
|
|||
|
|||
Insert spinning button with time format in Excel 2000?
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 |
#6
|
|||
|
|||
Insert spinning button with time format in Excel 2000?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data from Excel 2000 Worksheet with external links is not displayed when opened in Excel 2003 | Links and Linking in Excel | |||
Excel Time Manipulation | Excel Discussion (Misc queries) | |||
Can I insert excel spreadsheet in word and keep currency format? | Excel Discussion (Misc queries) | |||
how to convert GETPIVOTDATA from excel 2000 to excel 2002... | Excel Worksheet Functions | |||
DOS Data in Excel Format with Date and Military Time in same cell | Excel Worksheet Functions |