Is there a user form control for selecting a time?

  • Thread starter Thread starter Michelle
  • Start date Start date
M

Michelle

Hello, I want to be able to select a date and time in a user form

Is there a control that allows a user to select a time of day?

Also with dates... I know about he the calendar control, but that is only
available if users have Access installed

Is there an alternative that will work regardless?

Many thanks

M
 
Michelle

In the userform place a Textbox and copy the below code..To change the date
try up arrow and down arrow...Adjust to suit your requirement...

Private Sub UserForm_Activate()
Dim dtFormat As String
dtFormat = "dd-mmm-yyyy hh:mm"
Me.TextBox1 = Format(Now, dtFormat)
End Sub


Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
'JacobSkaria 1/15/2000
Dim intTemp, intDiff
Dim dtFormat As String
dtFormat = "dd-mmm-yyyy hh:mm"
If KeyCode = 38 Or KeyCode = 40 Then
intTemp = Me.TextBox1.SelStart
intDiff = IIf(KeyCode = 38, 1, -1)
Select Case intTemp
Case 0 To 2
Me.TextBox1 = Format(DateAdd("d", intDiff, TextBox1), dtFormat)
Case 3 To 6
Me.TextBox1 = Format(DateAdd("m", intDiff, TextBox1), dtFormat)
Case 7 To 11
Me.TextBox1 = Format(DateAdd("yyyy", intDiff, TextBox1), dtFormat)
Case 12 To 14
Me.TextBox1 = Format(DateAdd("h", intDiff, TextBox1), dtFormat)
Case 15 To 17
Me.TextBox1 = Format(DateAdd("n", intDiff, TextBox1), dtFormat)
End Select
KeyCode = 0
Me.TextBox1.SelStart = intTemp
End If
End Sub



If this post helps click Yes
 
Hi Michelle, there exists the Calendar Control 11.0 in the additional
controls in VB for excel. Just draw the control the same way you would draw a
cmd. I hope this will help solve the date problem, however the challenge is
making the calendar be the size of your choice.
 
nice Jacob. I'd probably use a calendar control and use the textbox solely
for the time. But i like your idea,
 
Back
Top