userform and formula
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.....
"Patrick Molloy" wrote:
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
"Patrick Molloy" wrote:
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
"jeramie" wrote:
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
|