| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding 1 hour to input box
How do I add an hour to what the user types into the input box of the ("Enter
start time 24-hour clock")? Private Sub Hourly_Click() Dim rng As Range Set rng = ActiveSheet.Cells(Rows.count, "A").End(xlUp).Offset(1, 0) rng.Value = InputBox("Enter start date mm/dd/yyyy") rng.Offset(0, 1).Value = InputBox("Enter start time 24-hour clock") rng.Offset(0, 1).Font.Bold = True rng.Offset(0, 2).Value = InputBox("Enter stop date mm/dd/yyyy") rng.Offset(0, 3).Value = InputBox("Enter stop time 24-hour clock") rng.Offset(0, 4).Value = InputBox("Enter TSN to increase") rng.Offset(0, 5).Value = InputBox("Enter TSN to decrease") rng.Offset(0, 6).Value = InputBox("Enter MW Amount") Set rng = Nothing End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
adding 1 hour to input box
add (1/24) or timeserial(1,0,0)
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "srroduin" wrote in message ... How do I add an hour to what the user types into the input box of the ("Enter start time 24-hour clock")? Private Sub Hourly_Click() Dim rng As Range Set rng = ActiveSheet.Cells(Rows.count, "A").End(xlUp).Offset(1, 0) rng.Value = InputBox("Enter start date mm/dd/yyyy") rng.Offset(0, 1).Value = InputBox("Enter start time 24-hour clock") rng.Offset(0, 1).Font.Bold = True rng.Offset(0, 2).Value = InputBox("Enter stop date mm/dd/yyyy") rng.Offset(0, 3).Value = InputBox("Enter stop time 24-hour clock") rng.Offset(0, 4).Value = InputBox("Enter TSN to increase") rng.Offset(0, 5).Value = InputBox("Enter TSN to decrease") rng.Offset(0, 6).Value = InputBox("Enter MW Amount") Set rng = Nothing End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
adding 1 hour to input box
how does that fit into the code? I keep getting errors.
"Bob Phillips" wrote: add (1/24) or timeserial(1,0,0) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "srroduin" wrote in message ... How do I add an hour to what the user types into the input box of the ("Enter start time 24-hour clock")? Private Sub Hourly_Click() Dim rng As Range Set rng = ActiveSheet.Cells(Rows.count, "A").End(xlUp).Offset(1, 0) rng.Value = InputBox("Enter start date mm/dd/yyyy") rng.Offset(0, 1).Value = InputBox("Enter start time 24-hour clock") rng.Offset(0, 1).Font.Bold = True rng.Offset(0, 2).Value = InputBox("Enter stop date mm/dd/yyyy") rng.Offset(0, 3).Value = InputBox("Enter stop time 24-hour clock") rng.Offset(0, 4).Value = InputBox("Enter TSN to increase") rng.Offset(0, 5).Value = InputBox("Enter TSN to decrease") rng.Offset(0, 6).Value = InputBox("Enter MW Amount") Set rng = Nothing End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
adding 1 hour to input box
Something like
With rng.Offset(0, 1) .Value = InputBox("Enter start time 24-hour clock") .Value = .Value + TimeSerail(1,0,0) .Font.Bold = True End With -- HTH Bob Phillips (remove nothere from email address if mailing direct) "srroduin" wrote in message ... how does that fit into the code? I keep getting errors. "Bob Phillips" wrote: add (1/24) or timeserial(1,0,0) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "srroduin" wrote in message ... How do I add an hour to what the user types into the input box of the ("Enter start time 24-hour clock")? Private Sub Hourly_Click() Dim rng As Range Set rng = ActiveSheet.Cells(Rows.count, "A").End(xlUp).Offset(1, 0) rng.Value = InputBox("Enter start date mm/dd/yyyy") rng.Offset(0, 1).Value = InputBox("Enter start time 24-hour clock") rng.Offset(0, 1).Font.Bold = True rng.Offset(0, 2).Value = InputBox("Enter stop date mm/dd/yyyy") rng.Offset(0, 3).Value = InputBox("Enter stop time 24-hour clock") rng.Offset(0, 4).Value = InputBox("Enter TSN to increase") rng.Offset(0, 5).Value = InputBox("Enter TSN to decrease") rng.Offset(0, 6).Value = InputBox("Enter MW Amount") Set rng = Nothing End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
adding 1 hour to input box
Thanks. Works great. I'm just learning this so bare with me.
"Bob Phillips" wrote: Something like With rng.Offset(0, 1) .Value = InputBox("Enter start time 24-hour clock") .Value = .Value + TimeSerail(1,0,0) .Font.Bold = True End With -- HTH Bob Phillips (remove nothere from email address if mailing direct) "srroduin" wrote in message ... how does that fit into the code? I keep getting errors. "Bob Phillips" wrote: add (1/24) or timeserial(1,0,0) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "srroduin" wrote in message ... How do I add an hour to what the user types into the input box of the ("Enter start time 24-hour clock")? Private Sub Hourly_Click() Dim rng As Range Set rng = ActiveSheet.Cells(Rows.count, "A").End(xlUp).Offset(1, 0) rng.Value = InputBox("Enter start date mm/dd/yyyy") rng.Offset(0, 1).Value = InputBox("Enter start time 24-hour clock") rng.Offset(0, 1).Font.Bold = True rng.Offset(0, 2).Value = InputBox("Enter stop date mm/dd/yyyy") rng.Offset(0, 3).Value = InputBox("Enter stop time 24-hour clock") rng.Offset(0, 4).Value = InputBox("Enter TSN to increase") rng.Offset(0, 5).Value = InputBox("Enter TSN to decrease") rng.Offset(0, 6).Value = InputBox("Enter MW Amount") Set rng = Nothing End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
adding 1 hour to input box
No problem. Just fire with next question when you are ready.
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "srroduin" wrote in message ... Thanks. Works great. I'm just learning this so bare with me. "Bob Phillips" wrote: Something like With rng.Offset(0, 1) .Value = InputBox("Enter start time 24-hour clock") .Value = .Value + TimeSerail(1,0,0) .Font.Bold = True End With -- HTH Bob Phillips (remove nothere from email address if mailing direct) "srroduin" wrote in message ... how does that fit into the code? I keep getting errors. "Bob Phillips" wrote: add (1/24) or timeserial(1,0,0) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "srroduin" wrote in message ... How do I add an hour to what the user types into the input box of the ("Enter start time 24-hour clock")? Private Sub Hourly_Click() Dim rng As Range Set rng = ActiveSheet.Cells(Rows.count, "A").End(xlUp).Offset(1, 0) rng.Value = InputBox("Enter start date mm/dd/yyyy") rng.Offset(0, 1).Value = InputBox("Enter start time 24-hour clock") rng.Offset(0, 1).Font.Bold = True rng.Offset(0, 2).Value = InputBox("Enter stop date mm/dd/yyyy") rng.Offset(0, 3).Value = InputBox("Enter stop time 24-hour clock") rng.Offset(0, 4).Value = InputBox("Enter TSN to increase") rng.Offset(0, 5).Value = InputBox("Enter TSN to decrease") rng.Offset(0, 6).Value = InputBox("Enter MW Amount") Set rng = Nothing End Sub |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
adding 1 hour to input box
I've got one more question for you. If the user types in the time to be for
instance 15:21....how do I make it so the minutes don't show up? "Bob Phillips" wrote: No problem. Just fire with next question when you are ready. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "srroduin" wrote in message ... Thanks. Works great. I'm just learning this so bare with me. "Bob Phillips" wrote: Something like With rng.Offset(0, 1) .Value = InputBox("Enter start time 24-hour clock") .Value = .Value + TimeSerail(1,0,0) .Font.Bold = True End With -- HTH Bob Phillips (remove nothere from email address if mailing direct) "srroduin" wrote in message ... how does that fit into the code? I keep getting errors. "Bob Phillips" wrote: add (1/24) or timeserial(1,0,0) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "srroduin" wrote in message ... How do I add an hour to what the user types into the input box of the ("Enter start time 24-hour clock")? Private Sub Hourly_Click() Dim rng As Range Set rng = ActiveSheet.Cells(Rows.count, "A").End(xlUp).Offset(1, 0) rng.Value = InputBox("Enter start date mm/dd/yyyy") rng.Offset(0, 1).Value = InputBox("Enter start time 24-hour clock") rng.Offset(0, 1).Font.Bold = True rng.Offset(0, 2).Value = InputBox("Enter stop date mm/dd/yyyy") rng.Offset(0, 3).Value = InputBox("Enter stop time 24-hour clock") rng.Offset(0, 4).Value = InputBox("Enter TSN to increase") rng.Offset(0, 5).Value = InputBox("Enter TSN to decrease") rng.Offset(0, 6).Value = InputBox("Enter MW Amount") Set rng = Nothing End Sub |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
adding 1 hour to input box
Format the cell as just hh (FormatCellsCustom), rather than the hh:mm it
is defaulting to. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "srroduin" wrote in message ... I've got one more question for you. If the user types in the time to be for instance 15:21....how do I make it so the minutes don't show up? "Bob Phillips" wrote: No problem. Just fire with next question when you are ready. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "srroduin" wrote in message ... Thanks. Works great. I'm just learning this so bare with me. "Bob Phillips" wrote: Something like With rng.Offset(0, 1) .Value = InputBox("Enter start time 24-hour clock") .Value = .Value + TimeSerail(1,0,0) .Font.Bold = True End With -- HTH Bob Phillips (remove nothere from email address if mailing direct) "srroduin" wrote in message ... how does that fit into the code? I keep getting errors. "Bob Phillips" wrote: add (1/24) or timeserial(1,0,0) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "srroduin" wrote in message ... How do I add an hour to what the user types into the input box of the ("Enter start time 24-hour clock")? Private Sub Hourly_Click() Dim rng As Range Set rng = ActiveSheet.Cells(Rows.count, "A").End(xlUp).Offset(1, 0) rng.Value = InputBox("Enter start date mm/dd/yyyy") rng.Offset(0, 1).Value = InputBox("Enter start time 24-hour clock") rng.Offset(0, 1).Font.Bold = True rng.Offset(0, 2).Value = InputBox("Enter stop date mm/dd/yyyy") rng.Offset(0, 3).Value = InputBox("Enter stop time 24-hour clock") rng.Offset(0, 4).Value = InputBox("Enter TSN to increase") rng.Offset(0, 5).Value = InputBox("Enter TSN to decrease") rng.Offset(0, 6).Value = InputBox("Enter MW Amount") Set rng = Nothing End Sub |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
adding 1 hour to input box
I had been trying variations of that before and I couldn't get it to work. I
could only get the hh like you suggested. This is for trading and they need to have hh:00. If I were the user I would just type in the correct date...buy my boss wants it dummy proof. Any ideas??? "Bob Phillips" wrote: Format the cell as just hh (FormatCellsCustom), rather than the hh:mm it is defaulting to. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "srroduin" wrote in message ... I've got one more question for you. If the user types in the time to be for instance 15:21....how do I make it so the minutes don't show up? "Bob Phillips" wrote: No problem. Just fire with next question when you are ready. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "srroduin" wrote in message ... Thanks. Works great. I'm just learning this so bare with me. "Bob Phillips" wrote: Something like With rng.Offset(0, 1) .Value = InputBox("Enter start time 24-hour clock") .Value = .Value + TimeSerail(1,0,0) .Font.Bold = True End With -- HTH Bob Phillips (remove nothere from email address if mailing direct) "srroduin" wrote in message ... how does that fit into the code? I keep getting errors. "Bob Phillips" wrote: add (1/24) or timeserial(1,0,0) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "srroduin" wrote in message ... How do I add an hour to what the user types into the input box of the ("Enter start time 24-hour clock")? Private Sub Hourly_Click() Dim rng As Range Set rng = ActiveSheet.Cells(Rows.count, "A").End(xlUp).Offset(1, 0) rng.Value = InputBox("Enter start date mm/dd/yyyy") rng.Offset(0, 1).Value = InputBox("Enter start time 24-hour clock") rng.Offset(0, 1).Font.Bold = True rng.Offset(0, 2).Value = InputBox("Enter stop date mm/dd/yyyy") rng.Offset(0, 3).Value = InputBox("Enter stop time 24-hour clock") rng.Offset(0, 4).Value = InputBox("Enter TSN to increase") rng.Offset(0, 5).Value = InputBox("Enter TSN to decrease") rng.Offset(0, 6).Value = InputBox("Enter MW Amount") Set rng = Nothing End Sub |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
adding 1 hour to input box
Do you mean they want 15:21 to show as 15:00, and say 15:49 also to show as
15:00? If so, use a custom format of hh":00" -- HTH Bob Phillips (remove nothere from email address if mailing direct) "srroduin" wrote in message ... I had been trying variations of that before and I couldn't get it to work. I could only get the hh like you suggested. This is for trading and they need to have hh:00. If I were the user I would just type in the correct date...buy my boss wants it dummy proof. Any ideas??? "Bob Phillips" wrote: Format the cell as just hh (FormatCellsCustom), rather than the hh:mm it is defaulting to. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "srroduin" wrote in message ... I've got one more question for you. If the user types in the time to be for instance 15:21....how do I make it so the minutes don't show up? "Bob Phillips" wrote: No problem. Just fire with next question when you are ready. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "srroduin" wrote in message ... Thanks. Works great. I'm just learning this so bare with me. "Bob Phillips" wrote: Something like With rng.Offset(0, 1) .Value = InputBox("Enter start time 24-hour clock") .Value = .Value + TimeSerail(1,0,0) .Font.Bold = True End With -- HTH Bob Phillips (remove nothere from email address if mailing direct) "srroduin" wrote in message ... how does that fit into the code? I keep getting errors. "Bob Phillips" wrote: add (1/24) or timeserial(1,0,0) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "srroduin" wrote in message ... How do I add an hour to what the user types into the input box of the ("Enter start time 24-hour clock")? Private Sub Hourly_Click() Dim rng As Range Set rng = ActiveSheet.Cells(Rows.count, "A").End(xlUp).Offset(1, 0) rng.Value = InputBox("Enter start date mm/dd/yyyy") rng.Offset(0, 1).Value = InputBox("Enter start time 24-hour clock") rng.Offset(0, 1).Font.Bold = True rng.Offset(0, 2).Value = InputBox("Enter stop date mm/dd/yyyy") rng.Offset(0, 3).Value = InputBox("Enter stop time 24-hour clock") rng.Offset(0, 4).Value = InputBox("Enter TSN to increase") rng.Offset(0, 5).Value = InputBox("Enter TSN to decrease") rng.Offset(0, 6).Value = InputBox("Enter MW Amount") Set rng = Nothing End Sub |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
adding 1 hour to input box
Thanks for all your help. It works great!
"Bob Phillips" wrote: Do you mean they want 15:21 to show as 15:00, and say 15:49 also to show as 15:00? If so, use a custom format of hh":00" -- HTH Bob Phillips (remove nothere from email address if mailing direct) "srroduin" wrote in message ... I had been trying variations of that before and I couldn't get it to work. I could only get the hh like you suggested. This is for trading and they need to have hh:00. If I were the user I would just type in the correct date...buy my boss wants it dummy proof. Any ideas??? "Bob Phillips" wrote: Format the cell as just hh (FormatCellsCustom), rather than the hh:mm it is defaulting to. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "srroduin" wrote in message ... I've got one more question for you. If the user types in the time to be for instance 15:21....how do I make it so the minutes don't show up? "Bob Phillips" wrote: No problem. Just fire with next question when you are ready. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "srroduin" wrote in message ... Thanks. Works great. I'm just learning this so bare with me. "Bob Phillips" wrote: Something like With rng.Offset(0, 1) .Value = InputBox("Enter start time 24-hour clock") .Value = .Value + TimeSerail(1,0,0) .Font.Bold = True End With -- HTH Bob Phillips (remove nothere from email address if mailing direct) "srroduin" wrote in message ... how does that fit into the code? I keep getting errors. "Bob Phillips" wrote: add (1/24) or timeserial(1,0,0) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "srroduin" wrote in message ... How do I add an hour to what the user types into the input box of the ("Enter start time 24-hour clock")? Private Sub Hourly_Click() Dim rng As Range Set rng = ActiveSheet.Cells(Rows.count, "A").End(xlUp).Offset(1, 0) rng.Value = InputBox("Enter start date mm/dd/yyyy") rng.Offset(0, 1).Value = InputBox("Enter start time 24-hour clock") rng.Offset(0, 1).Font.Bold = True rng.Offset(0, 2).Value = InputBox("Enter stop date mm/dd/yyyy") rng.Offset(0, 3).Value = InputBox("Enter stop time 24-hour clock") rng.Offset(0, 4).Value = InputBox("Enter TSN to increase") rng.Offset(0, 5).Value = InputBox("Enter TSN to decrease") rng.Offset(0, 6).Value = InputBox("Enter MW Amount") Set rng = Nothing End Sub |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
adding 1 hour to input box
I now have the following code. I added the bottom part starting with For x =
1 to 24. I am trying to make it loop until the inputed start time = the inputed final stop time. The way it is now it is a continuous loop. Any ideas??? Private Sub Hourly_Click() Dim rng As Range Set rng = ActiveSheet.Cells(Rows.count, "A").End(xlUp).Offset(1, 0) rng.Value = InputBox("Enter TSN to increase") rng.Offset(0, 1).Value = InputBox("Enter TSN to decrease") rng.Offset(0, 2).Value = InputBox("Enter initial start date mm/dd/yyyy") With rng.Offset(0, 10) .Value = InputBox("Enter initial start time, use 24-hour clock time") .Value = .Value + TimeSerial(1, 0, 0) End With rng.Offset(0, 4).Value = InputBox("Enter final stop date mm/dd/yyyy") With rng.Offset(0, 11) .Value = InputBox("Enter final stop time, use 24-hour clock time") .Value = .Value + TimeSerial(1, 0, 0) End With For x = 1 To 24 rng.Offset(0, 3).Value = InputBox("Enter start time, use 24-hour clock time") rng.Offset(0, 5).Value = InputBox("Enter stop time, use 24-hour clock time") rng.Offset(0, 6).Value = InputBox("Enter MW Amount") If rng.Offset(0, 3).Value = rng.Offset(0, 11).value Then End If Next x Set rng = Nothing End Sub "Bob Phillips" wrote: Do you mean they want 15:21 to show as 15:00, and say 15:49 also to show as 15:00? If so, use a custom format of hh":00" -- HTH Bob Phillips (remove nothere from email address if mailing direct) "srroduin" wrote in message ... I had been trying variations of that before and I couldn't get it to work. I could only get the hh like you suggested. This is for trading and they need to have hh:00. If I were the user I would just type in the correct date...buy my boss wants it dummy proof. Any ideas??? "Bob Phillips" wrote: Format the cell as just hh (FormatCellsCustom), rather than the hh:mm it is defaulting to. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "srroduin" wrote in message ... I've got one more question for you. If the user types in the time to be for instance 15:21....how do I make it so the minutes don't show up? "Bob Phillips" wrote: No problem. Just fire with next question when you are ready. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "srroduin" wrote in message ... Thanks. Works great. I'm just learning this so bare with me. "Bob Phillips" wrote: Something like With rng.Offset(0, 1) .Value = InputBox("Enter start time 24-hour clock") .Value = .Value + TimeSerail(1,0,0) .Font.Bold = True End With -- HTH Bob Phillips (remove nothere from email address if mailing direct) "srroduin" wrote in message ... how does that fit into the code? I keep getting errors. "Bob Phillips" wrote: add (1/24) or timeserial(1,0,0) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "srroduin" wrote in message ... How do I add an hour to what the user types into the input box of the ("Enter start time 24-hour clock")? Private Sub Hourly_Click() Dim rng As Range Set rng = ActiveSheet.Cells(Rows.count, "A").End(xlUp).Offset(1, 0) rng.Value = InputBox("Enter start date mm/dd/yyyy") rng.Offset(0, 1).Value = InputBox("Enter start time 24-hour clock") rng.Offset(0, 1).Font.Bold = True rng.Offset(0, 2).Value = InputBox("Enter stop date mm/dd/yyyy") rng.Offset(0, 3).Value = InputBox("Enter stop time 24-hour clock") rng.Offset(0, 4).Value = InputBox("Enter TSN to increase") rng.Offset(0, 5).Value = InputBox("Enter TSN to decrease") rng.Offset(0, 6).Value = InputBox("Enter MW Amount") Set rng = Nothing End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I subtract 1 hour from one cell and input it another? | Excel Worksheet Functions | |||
| help adding up hour totals on a logbook | Excel Discussion (Misc queries) | |||
| Adding time over a 24 hour period | Excel Discussion (Misc queries) | |||
| Adding on a 24 hour clock basis | Excel Worksheet Functions | |||
| Adding hundreds of hour of time | Excel Programming | |||
Linear Mode
