Help with VBA in a UserForm WIN7 Excel2007

ozzborn

Board Regular
Joined
Sep 14, 2011
Messages
64
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.

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
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

CharlesChuckieCharles

Well-known Member
Joined
May 10, 2011
Messages
2,153
Change

txtDays.Value = DateDiff("d", txtStart.Value, txtEnd.Value)


to

txtDays.Value = DateDiff("d", (txtStart.Value)-1, txtEnd.Value)

to fix the count

Not sure why you are getting error 13

need to post all of your code
 

ozzborn

Board Regular
Joined
Sep 14, 2011
Messages
64
Charles,

Thank you for your help. I tried your code and I received "Error 13" when I tab from txtStart to txtEnd. I was also thinking that should it not be +1? ie currently the way it works. 1 Jan 15 to 5 Jan 15 returns 4 days, where I want it to return 5 days inclusive of the start and end date.

In regards to post all my code. I did post all my code in my original listing. I am a super big newbie so I really do not know what I am doing. Pardon my inexperience.

Here is the code as it is. As I said when I tried your code it did not work but then I might not be entering the code correctly.

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
 

CharlesChuckieCharles

Well-known Member
Joined
May 10, 2011
Messages
2,153
The problem is using a text box you need to check your dates are dates


Replace every occurance of

txtdays.Value = DateDiff("d", DateValue(txtstart.Value), DateValue(txtend.Value))



Code:
If IsDate(txtstart.Value) And IsDate(txtend.Value) Then
    txtdays.Value = DateDiff("d", DateValue(txtstart.Value) - 1, DateValue(txtend.Value))
Else
   txtstart = ""
   txtend = ""
   txtstart.SetFocus
End If


in the cmdadddata

use

Code:
On Error Resume Next
If IsDate(txtstart.Value) And IsDate(txtend.Value) Then
    txtdays.Value = DateDiff("d", DateValue(txtstart.Value) - 1, DateValue(txtend.Value))
Else
   txtstart = ""
   txtend = ""
   txtstart.SetFocus
   [B]Exit Sub[/B]
   
End If
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,910

ADVERTISEMENT

The value in a TextBox is a string. It has to be converted to a Date to perform calculations.
You could use code like this to both validate entries and to put the difference in txtDate.
Alter the default format to suit your taste.

Excel has several automatic type conversion routines built in.
A coder loses nothing by explicitly calling conversion routines, but gains clarity as to exactly what one is doing.

Code:
Private Sub txtStartDate_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    With txtStartDate
        If IsDate(.Text) Then
            .Text = Format(DateValue(.Text), "dd mmm yyyy")
            If Me.txtStartDate <> vbNullString And Me.txtEndDate.Text <> vbNullString Then
                Me.txtDays.Text = DateValue(Me.txtEndDate.Text) - DateValue(Me.txtStartDate.Text)
            Else
                Me.txtDays.Text = vbNullString
            End If
        Else
            MsgBox "Please enter a date"
            .SelStart = 0: .SelLength = Len(.Text)
            Cancel = True
        End If
    End With
End Sub

Private Sub txtEndDate_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    With Me.txtEndDate
        If IsDate(.Text) Then
            .Text = Format(DateValue(.Text), "dd mmm yyyy")
            If Me.txtStartDate <> vbNullString And Me.txtEndDate.Text <> vbNullString Then
                Me.txtDays.Text = DateValue(Me.txtEndDate.Text) - DateValue(Me.txtStartDate.Text)
            Else
                Me.txtDays.Text = vbNullString
            End If
        Else
            MsgBox "Please enter a date"
            .SelStart = 0: .SelLength = Len(.Text)
            Cancel = True
        End If
    End With
End Sub
 
Last edited:

ozzborn

Board Regular
Joined
Sep 14, 2011
Messages
64
Charles,

THANK YOU!!!

It worked. I think I got it all in correct. At least the math works and I no longer receive an Run time Error 13 when I use the mouse to go back to txtStart and txtEnd text blocks.

I have included what the code looks like now. seems to work very smoothly. How does it look to you?

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
If IsDate(txtStart.Value) And IsDate(txtEnd.Value) Then
    txtDays.Value = DateDiff("d", DateValue(txtStart.Value) - 1, DateValue(txtEnd.Value))
Else
   txtStart = ""
   txtEnd = ""
   txtStart.SetFocus
   Exit Sub

End If

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)

If IsDate(txtStart.Value) And IsDate(txtEnd.Value) Then
    txtDays.Value = DateDiff("d", DateValue(txtStart.Value) - 1, DateValue(txtEnd.Value))
Else
   txtStart = ""
   txtEnd = ""
   txtStart.SetFocus
End If
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
 

ozzborn

Board Regular
Joined
Sep 14, 2011
Messages
64
Mike,

I have not had the time to go through your code yet. It looks a bit more complicated and I am a super newbie. I will try and work through it and see how it works too. Thank you for your assistance. I do appreciate all help.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,689
Messages
5,597,556
Members
414,155
Latest member
Grainne whiteside

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top