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
---------------
Jacob Skaria


"Michelle" wrote:

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

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.
--
Knowing makes you smart but doing sets you apart


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Michelle" wrote:

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

nice Jacob. I'd probably use a calendar control and use the textbox solely
for the time. But i like your idea,

"Jacob Skaria" wrote in message
...
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
---------------
Jacob Skaria


"Michelle" wrote:

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

You could try a DTPicker.


On Jun 30, 11:31*am, "Michelle" wrote:
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