Juicey27
09-28-2010, 10:49 AM
I have two date pickers on one userform
dtpDate for the date
dtpTime for the time
User is able to select date and time
In my userform intialize statement i have added the code so that the dtpDate defaults to the current date. This works.
dtpDate.Value = Now
After having entered this code, everytime I click on my 'submit' button, the time ends up being the current time, instead of allowing the user to enter whatever time they desire. Even if the user enters a time, the form converts this time to whatever time it really is!!:banghead: I need the dtpTime to reflect what is ever actually in the box, not the current time.
I have included my code for both the userform initialize & the submit button i mentioned.
Submit button code:
Private Sub cmdSubmit_Click()
Dim sh As Worksheet
Dim dunk As String
dunk = MsgBox("Are you sure you want to submit this session?", vbYesNo Or vbQuestion)
If dunk = vbYes Then
On Error Resume Next
Set sh = ActiveWorkbook.Worksheets(cboNames.Value)
On Error GoTo 0
Else
MsgBox "Returning to coaching form. Please proceed."
End If
If sh Is Nothing Then Exit Sub
sh.Activate
Range("A4").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = cboNames.Value
ActiveCell.Offset(0, 5) = cboMethod.Value
ActiveCell.Offset(0, 6) = IIf(optYes1.Value, 1, IIf(optNo1.Value, 0, IIf(optNA1.Value, -1, "")))
ActiveCell.Offset(0, 7) = IIf(optYes2.Value, 1, IIf(optNo2.Value, 0, IIf(optNA2.Value, -1, "")))
ActiveCell.Offset(0, 8) = IIf(optYes3.Value, 1, IIf(optNo3.Value, 0, IIf(optNA3.Value, -1, "")))
ActiveCell.Offset(0, 9) = IIf(optYes4.Value, 1, IIf(optNo4.Value, 0, IIf(optNA4.Value, -1, "")))
ActiveCell.Offset(0, 10) = IIf(optYes5.Value, 1, IIf(optNo5.Value, 0, IIf(optNA5.Value, -1, "")))
ActiveCell.Offset(0, 11) = IIf(optYes6.Value, 1, IIf(optNo6.Value, 0, IIf(optNA6.Value, -1, "")))
ActiveCell.Offset(0, 12) = IIf(optYes7.Value, 1, IIf(optNo7.Value, 0, IIf(optNA7.Value, -1, "")))
ActiveCell.Offset(0, 13) = IIf(optYes8.Value, 1, IIf(optNo8.Value, 0, IIf(optNA8.Value, -1, "")))
ActiveCell.Offset(0, 1) = DTPicker1.Value
ActiveCell.Offset(0, 4) = cboCoaches.Value
ActiveCell.Offset(0, 15) = txtComments.Value
ActiveCell.Offset(0, 2) = txtPolicyNumber.Value
UserForm1.Hide
Call UserForm_Initialize
End Sub
Userform Intialize code
Private Sub UserForm_Initialize()
txtComments.Value = ""
txtPolicyNumber.Value = ""
optYes1.Value = False
optNo1.Value = False
optNA1.Value = False
optYes2.Value = False
optNo2.Value = False
optNA2.Value = False
optYes3.Value = False
optNo3.Value = False
optNA3.Value = False
optYes4.Value = False
optNo4.Value = False
optNA4.Value = False
optYes5.Value = False
optNo5.Value = False
optNA5.Value = False
optYes6.Value = False
optNo6.Value = False
optNA6.Value = False
optYes7.Value = False
optNo7.Value = False
optNA7.Value = False
optYes8.Value = False
optNo8.Value = False
optNA8.Value = False
cboNames.SetFocus
With cboNames
.Clear
.AddItem "Alexandra Morales"
.AddItem "Angela Ferguson"
.AddItem "Carol Usay"
.AddItem "Edith Garcia"
.AddItem "Ericka Cliff"
.AddItem "Jennifer Switkowski"
.AddItem "Karen Lewis"
.AddItem "Katrina Kostakis"
.AddItem "Mac Griffin"
.AddItem "Melissa Rivera"
.AddItem "Michelle Gainer"
.AddItem "Vickie Robinson"
.AddItem "Virginia Gilchrist"
.AddItem "Marielee Santiago"
.AddItem "Paul Deutsch"
End With
cboNames.Value = ""
With cboCoaches
.Clear
.AddItem "Angela Ferguson"
.AddItem "Jennifer Switkowski"
.AddItem "Karen Lewis"
.AddItem "Derek Smith"
.AddItem "Sam Liotta"
End With
cboCoaches.Value = ""
With cboMethod
.Clear
.AddItem "Phone Call"
.AddItem "Callback"
End With
cboMethod.Value = ""
End Sub
Please help. Any ideas are welcome. Thanks.
dtpDate for the date
dtpTime for the time
User is able to select date and time
In my userform intialize statement i have added the code so that the dtpDate defaults to the current date. This works.
dtpDate.Value = Now
After having entered this code, everytime I click on my 'submit' button, the time ends up being the current time, instead of allowing the user to enter whatever time they desire. Even if the user enters a time, the form converts this time to whatever time it really is!!:banghead: I need the dtpTime to reflect what is ever actually in the box, not the current time.
I have included my code for both the userform initialize & the submit button i mentioned.
Submit button code:
Private Sub cmdSubmit_Click()
Dim sh As Worksheet
Dim dunk As String
dunk = MsgBox("Are you sure you want to submit this session?", vbYesNo Or vbQuestion)
If dunk = vbYes Then
On Error Resume Next
Set sh = ActiveWorkbook.Worksheets(cboNames.Value)
On Error GoTo 0
Else
MsgBox "Returning to coaching form. Please proceed."
End If
If sh Is Nothing Then Exit Sub
sh.Activate
Range("A4").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = cboNames.Value
ActiveCell.Offset(0, 5) = cboMethod.Value
ActiveCell.Offset(0, 6) = IIf(optYes1.Value, 1, IIf(optNo1.Value, 0, IIf(optNA1.Value, -1, "")))
ActiveCell.Offset(0, 7) = IIf(optYes2.Value, 1, IIf(optNo2.Value, 0, IIf(optNA2.Value, -1, "")))
ActiveCell.Offset(0, 8) = IIf(optYes3.Value, 1, IIf(optNo3.Value, 0, IIf(optNA3.Value, -1, "")))
ActiveCell.Offset(0, 9) = IIf(optYes4.Value, 1, IIf(optNo4.Value, 0, IIf(optNA4.Value, -1, "")))
ActiveCell.Offset(0, 10) = IIf(optYes5.Value, 1, IIf(optNo5.Value, 0, IIf(optNA5.Value, -1, "")))
ActiveCell.Offset(0, 11) = IIf(optYes6.Value, 1, IIf(optNo6.Value, 0, IIf(optNA6.Value, -1, "")))
ActiveCell.Offset(0, 12) = IIf(optYes7.Value, 1, IIf(optNo7.Value, 0, IIf(optNA7.Value, -1, "")))
ActiveCell.Offset(0, 13) = IIf(optYes8.Value, 1, IIf(optNo8.Value, 0, IIf(optNA8.Value, -1, "")))
ActiveCell.Offset(0, 1) = DTPicker1.Value
ActiveCell.Offset(0, 4) = cboCoaches.Value
ActiveCell.Offset(0, 15) = txtComments.Value
ActiveCell.Offset(0, 2) = txtPolicyNumber.Value
UserForm1.Hide
Call UserForm_Initialize
End Sub
Userform Intialize code
Private Sub UserForm_Initialize()
txtComments.Value = ""
txtPolicyNumber.Value = ""
optYes1.Value = False
optNo1.Value = False
optNA1.Value = False
optYes2.Value = False
optNo2.Value = False
optNA2.Value = False
optYes3.Value = False
optNo3.Value = False
optNA3.Value = False
optYes4.Value = False
optNo4.Value = False
optNA4.Value = False
optYes5.Value = False
optNo5.Value = False
optNA5.Value = False
optYes6.Value = False
optNo6.Value = False
optNA6.Value = False
optYes7.Value = False
optNo7.Value = False
optNA7.Value = False
optYes8.Value = False
optNo8.Value = False
optNA8.Value = False
cboNames.SetFocus
With cboNames
.Clear
.AddItem "Alexandra Morales"
.AddItem "Angela Ferguson"
.AddItem "Carol Usay"
.AddItem "Edith Garcia"
.AddItem "Ericka Cliff"
.AddItem "Jennifer Switkowski"
.AddItem "Karen Lewis"
.AddItem "Katrina Kostakis"
.AddItem "Mac Griffin"
.AddItem "Melissa Rivera"
.AddItem "Michelle Gainer"
.AddItem "Vickie Robinson"
.AddItem "Virginia Gilchrist"
.AddItem "Marielee Santiago"
.AddItem "Paul Deutsch"
End With
cboNames.Value = ""
With cboCoaches
.Clear
.AddItem "Angela Ferguson"
.AddItem "Jennifer Switkowski"
.AddItem "Karen Lewis"
.AddItem "Derek Smith"
.AddItem "Sam Liotta"
End With
cboCoaches.Value = ""
With cboMethod
.Clear
.AddItem "Phone Call"
.AddItem "Callback"
End With
cboMethod.Value = ""
End Sub
Please help. Any ideas are welcome. Thanks.