adding 1 hour to input box

S

srroduin

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
 
B

Bob Phillips

add (1/24) or timeserial(1,0,0)



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
B

Bob Phillips

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)
 
B

Bob Phillips

No problem. Just fire with next question when you are ready.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
S

srroduin

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?
 
B

Bob Phillips

Format the cell as just hh (Format>Cells>Custom), rather than the hh:mm it
is defaulting to.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
S

srroduin

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???
 
B

Bob Phillips

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)
 
S

srroduin

Thanks for all your help. It works great!

Bob Phillips said:
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)
 
S

srroduin

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top