I have some code with several problems I need help with.
1. I believe I need some error correction help. When I am putting dates into txtStart and txtEnd it works fine but if I tab out of either one and then go back using the mouse I receive a Run-Time error '13' Type mismatch.
2. The math code that I have subtracting dates to post the days between days works but it does not count all the days correctly.
The way leave is calculated and tracked in our office is that the Start date is the 1st day you are off and the end date is the last day your off. The way the code above works it does not calculate the last day and it would be confusing for the employees to have to put in the return date. How can I correct the math code that fixes this problem. ie "1 Jan 15" to "5 Jan 15" is 5 days of leave but the code above counts it as 4 days between the dates.
Below is all the code I am currently using for the UserForm. Thanks
1. I believe I need some error correction help. When I am putting dates into txtStart and txtEnd it works fine but if I tab out of either one and then go back using the mouse I receive a Run-Time error '13' Type mismatch.
2. The math code that I have subtracting dates to post the days between days works but it does not count all the days correctly.
Code:
txtDays.Value = DateDiff("d", txtStart.Value, txtEnd.Value)
The way leave is calculated and tracked in our office is that the Start date is the 1st day you are off and the end date is the last day your off. The way the code above works it does not calculate the last day and it would be confusing for the employees to have to put in the return date. How can I correct the math code that fixes this problem. ie "1 Jan 15" to "5 Jan 15" is 5 days of leave but the code above counts it as 4 days between the dates.
Below is all the code I am currently using for the UserForm. Thanks
Code:
Private Sub cmdAddData_Click()
Dim ws As Worksheet
Dim Addto As Range
Set ws = Sheet2
Set Addto = ws.Range("c65356").End(xlUp).Offset(1, 0)
On Error Resume Next
txtDays.Value = DateDiff("d", txtStart.Value, txtEnd.Value)
On Error GoTo 0
With ws
Addto = txtDate.Value
Addto.Offset(0, 1).Value = cboName.Value
Addto.Offset(0, 2).Value = txtStart.Value
Addto.Offset(0, 3).Value = txtEnd.Value
Addto.Offset(0, 4).Value = txtDays.Value
End With
lstMyData.Value = ""
txtDate.Value = Format(Date, "Medium Date")
cboName.Value = ""
txtStart.Value = ""
txtEnd.Value = ""
txtDays.Value = ""
End Sub
Private Sub txtEnd_Exit(ByVal Cancel As MSForms.ReturnBoolean)
txtDays.Value = DateDiff("d", txtStart.Value, txtEnd.Value)
End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub
Private Sub cmdView_Click()
Unload Me
Sheet2.Select
Sheet2.Range("A1").Select
End Sub
Private Sub UserForm_Click()
End Sub
Private Sub UserForm_Initialize()
txtDate.Value = Format(Date, "Medium Date")
Me.cboName.SetFocus
End Sub