-
IsDate serves as an IsTime function.
You might try this code. Get a userform with a TextBox and a Scroll Bar and this code in the userform's module.
The ScrollBar1_Change and TextBox1_AfterUpdate events link those two controls.
The TextBox1_KeyPress event prevents the user from entering a non-time.
The TextBox1_AfterUpdate takes the user entered time and converts it to a standard format.
The UserForm_Intialize event setsup the limits on the scroll bar, depending on whether seconds or minutes is the shortest interval of interest.
[VBA]Dim formatString As String
Private Sub ScrollBar1_Change()
TextBox1.Text = Format(ScrollBar1.Value / ScrollBar1.Max, formatString)
End Sub
Private Sub TextBox1_AfterUpdate()
With TextBox1
If IsDate(.Text) Then
.Text = Format(TimeValue(.Text), formatString)
ElseIf IsDate(.Text & "a") Then
.Text = Format(TimeValue(.Text & "a"), formatString)
ElseIf IsDate(.Text & "0") Then
.Text = Format(TimeValue(.Text & "0"), formatString)
End If
ScrollBar1.Value = CDbl(TimeValue(.Text)) * ScrollBar1.Max
End With
End Sub
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Dim newText As String
With TextBox1
newText = Left(.Text, .SelStart) & Chr(KeyAscii) & Mid(.Text, .SelStart + .SelLength + 1)
End With
If Not (IsDate(newText) Or IsDate(newText & "0") Or IsDate(newText & "A")) Then
KeyAscii = 0
End If
End Sub
Private Sub UserForm_Initialize()
Rem format scrollbar for minutes
Dim minuteBased As Boolean, secondBased As Boolean
Dim timeFactor As Double
minuteBased = True: secondBased = False
If minuteBased Then
formatString = "h:mm AM/PM"
timeFactor = 24 * 60
ElseIf secondBased Then
formatString = "h:mm:ss AM/PM"
timeFactor = 24 * 60 * 60#
End If
With ScrollBar1
.Min = 0
.Max = timeFactor
.SmallChange = 1
.LargeChange = 60
.Value = 0
End With
End Sub[/VBA]
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules