Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > userform and formula
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 18
Default userform and formula
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,298
Default userform and formula
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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,298
Default userform and formula
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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 18
Default userform and formula
The cell is formatted for time. I need the form to take 1330 from the
textbox, and put 13:30 in the cell.

"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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 18
Default 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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 18
Default userform and formula
thanks patrick. i got it pasted in there correctly now. works great.

"jeramie" wrote:

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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Userform button setting variable from formula teepee[_4_] Excel Programming 2 January 4th 06 06:59 PM
Userform button setting variable from formula teepee Excel Discussion (Misc queries) 0 January 3rd 06 08:26 PM
refer to a userform in a formula TimT Excel Programming 7 July 9th 05 12:05 AM
Userform with Formula Curare[_5_] Excel Programming 0 August 16th 04 01:33 AM
Userform Formula Help Needed timh2ofall Excel Programming 2 December 12th 03 10:55 PM


All times are GMT +1. The time now is 08:24 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement