userform and formula

[jeramie]

I'm new at this, but I'v managed to cobble this together. when a user enters
a time into textbox1 they enter it as 1300 (1:00 PM), or 0800 (8:00 AM). I
need it to be changed from 1300 to 13:00 and entered into cell C5. I can't
seem to get it to work.

Any Help would be greatly appreciated.

Thanks, in advance

Private Sub CommandButton1_Click()

Dim Stime As String
Dim Etime As String
Stime = Find(Mid(TextBox1.Text, 1, 2) & ":" & Mid(TextBox1.Text, 3, 2))
Etime = .Find(Mid(TextBox2.Text, 1, 2) & ":" & Mid(TextBox2.Text, 3, 2))

With Worksheets("sheet1")
If TextBox3.Text <> "" Then
Range("A3").Select
Application.Selection.Value = TextBox3.Text
Cancel = False
Else: MsgBox "Please enter your name in the box."
End If

If TextBox4.Text <> "" Then
Range("A7").Select
Application.Selection.Value = TextBox4.Text
Cancel = False
Else: MsgBox "Please enter the name of the officer who will relieve
you"
End If

If TextBox5.Text <> "" Then
Range("A9").Select
Application.Selection.Value = TextBox5.Text
Cancel = False
Else: MsgBox "Please enter the name of the officer you relieved"
End If

If TextBox1.Text <> "" Then
Range("C5").Select
Application.Selection.Value = Stime
Cancel = False
End With
Else: MsgBox "Please enter the time your shift starts"
End If

If TextBox2.Text <> "" Then
Range("D5").Select
Application.Selection.Value = TextBox2.Text
Cancel = False
Else: MsgBox "Please enter the time your shift ends"
End If

End With
TextBox1.Text = Clear
TextBox2.Text = Clear
TextBox3.Text = Clear
TextBox4.Text = Clear
TextBox5.Text = Clear
UserForm1.Hide
End Sub

 

[Patrick Molloy]

the key is that "time" is saved as the decimal part of the number eg 12noon
is 0.5 and 6pm is 0.75

Private Sub CommandButton1_Click()
Dim thisTime As Double
Dim entry As String
If IsNumeric(TextBox1.Text) Then
thisTime = CDbl(TextBox1.Text) / 2400
With Range("A1")
.NumberFormat = "HH:MM"
.Value = thisTime
End With
End If
End Sub

 

[Patrick Molloy]

whoops. forgot to convert the minutes! sorry. Here's a correction.
Method if say 825 is entered, make it 0825. convert the 08 bit to decimal by
dividing by 24, then make the minutes into hours by dividing by 60 and then
decimal by dividing by 24...

Option Explicit

Private Sub CommandButton1_Click()
Dim EnteredTime As String
Dim thisTime As Double
Dim entry As String
EnteredTime = TextBox1.Text
If Len(EnteredTime) = 3 Then
EnteredTime = "0" & EnteredTime
End If
If IsNumeric(EnteredTime) Then
thisTime = CDbl(Left(EnteredTime, 2)) / 24 + CDbl(Right(EnteredTime,
2)) / 24 / 60
With Range("A1")
.NumberFormat = "HH:MM"
.Value = thisTime
End With
End If

End Sub

note that the only cjeck I make is that the variable enteredTime must be
numeric. there's no other check for the sake of simplicity. However, if you
wanted, something like this might be the way to go

Option Explicit

Private Sub CommandButton1_Click()
Dim EnteredTime As String
Dim ThisTime As Double
Dim entry As String
EnteredTime = TextBox1.Text
If Len(EnteredTime) = 3 Then
EnteredTime = "0" & EnteredTime
End If
If IsValidTime(EnteredTime) Then
ThisTime = CDbl(Left(EnteredTime, 2)) / 24 + CDbl(Right(EnteredTime,
2)) / 24 / 60
With Range("A1")
.NumberFormat = "HH:MM"
.Value = ThisTime
End With
Else
MsgBox "Invalid Time in textbox1"
End If

End Sub
Private Function IsValidTime(ThisTime As String) As Boolean
Dim hours As Double, minutes As Double
If IsNumeric(ThisTime) Then
If Len(ThisTime) = 3 Then ThisTime = "0" & ThisTime
If Len(ThisTime) = 4 Then
hours = CDbl(Left(ThisTime, 2))
If hours <= 24 Then
minutes = CDbl(Right(ThisTime, 2))
If minutes < 60 Then
IsValidTime = True
End If
End If
End If
End If
End Function

 

[jeramie]

The cell is formatted for time. I need the form to take 1330 from the
textbox, and put 13:30 in the cell.

 

[jeramie]

what i was trying to do was take the 1330 in textbox1, split it in half, and
put a : in the middle. i've been scavenging code from all over to make things
work.....

 

[jeramie]

thanks patrick. i got it pasted in there correctly now. works great.