Excel vba 2007 Type 13 mismatch with dates in a texbox of Userform

[F0RE5T]

Excel 2007 userform and VBA

below is my code used in a userform text box change event. This is one field that helps allocate individuals to an activity for a period of days.

TxbAllocBEDateOrig.Value is a copy of the original date recorded incase the user enters an incorrect date.

TxbAllocBEDate.Value is the original date that is amended
TxbAllocBSDate.Value is the 'start date"
vpstartdate is a global variable declared as a date
vpenddate is a global variable declared as a date
The purpose is to (a) check the date entered / amended meets certain critera e.g. TxbAllocBEDate.Value is not prior to TxbAllocBSDate.Value

On opening the userform data is retrieved and stored in fields with no issues.
When I amend the date held in TxbAllocBEDate.Value I keep getting a type mismatch error 13 on the following lines:
vpEndDate = DateValue(TxbAllocBEDate.Value)

Any ideas why????

I use a similar process on another field to check the date amendment and no problems.

many thanks in advance

Forest

Private Sub TxbAllocBEDate_Change()
    Dim vFYHolidays As Range
    Dim vEntitleDeductDays As Integer
    Dim vAllocdaysOrig As Integer


    
   ' If vpOptions = True Then GoTo MyExit
    If Len(TxbAllocBEDate.Value) = 10 Then
    
    
        Set vFYHolidays = Worksheets("SysData").Range("V7:V16")
        
        vEntitleDeductDays = 0
             
        If TxbAllocDays.Value > "" Then vAllocdaysOrig = TxbAllocDays.Value
               
        If TxbAllocBEDate.Value > " " Then
            vpStartDate = DateValue(TxbAllocBSDate.Value)   'global variable declared as date
            vpEndDate = DateValue(TxbAllocBEDate.Value)     'global variable declared as date
            
            If vpStartDate = vpEndDate Then
                vEntitleDeductDays = 1
                GoTo CheckDates
            Else
'to calculate remaining entittlement
                vEntitleDeductDays = vEntitleDeductDays + CDec(WorksheetFunction.NetworkDays(vpStartDate, vpEndDate, vFYHolidays))
                If vEntitleDeductDays <= 0 Then
                    vpResponse = MsgBox("Allocation End Date before Start Date!", vbInformation, "Project Allocaion Date Error")
                    TxbAllocBEDate.SetFocus
                    GoTo MyExit
                Else
CheckDates:
         'total days for calendar on saving
                    vpDecimal = CDec(DateDiff("d", vpStartDate, vpEndDate))   ' + 1)
        'below changes netdays!!!!!
                    If OptbAllocNA = True Then
                        TxbAllocDays = vEntitleDeductDays
                        TxbAllocTotDays = vpDecimal 'this box is not visible TxbAllocDays
                    Else
                        TxbAllocDays = vEntitleDeductDays - 0.5 '
                        TxbAllocTotDays = vpDecimal - 0.5  'this box is not visible TxbAllocDays
                    End If
                    vpStartDate = DateValue(TxbAllocBEDate.Value)
                    vpEndDate = DateValue(TxbAllocBEDateOrig.Value)
                    If vpStartDate > vpEndDate Then
                        vpResponse = MsgBox("Allocation 'End Date' is post to Project End Date" & _
                        vbCr & "Is this authorised", vbYesNo, "Late Allocation Date")
                        If vpResponse = 7 Then
                            TxbAllocBEDate.Value = Format(vpEndDate, "dd/mm/yyyy")    '
                            TxbAllocDays.Value = vAllocdaysOrig
                            GoTo MyExit
                        End If
                    End If
                End If
            End If
        End If
    End If
MyExit:
vpOptions = False
End Sub

 

[RickXL]

Hi,

My guess would be that TxbAllocBEDate.Value is not in a date format that DateValue can recognise.

Just create a little test module like this:

Sub Test()
    Dim myDate    As Date
    Dim myString  As String
    
    myString = "91/02/2016"
    myDate = DateValue(myString)
    MsgBox myString & vbLf & myDate
End Sub

That example produces an Error 13 - Mismatch.
Corrrect the date and try again.

You need to set the string equal to what you enter into TxbAllocBEDate and see what happens.

Regards,

 

[F0RE5T]

Hi

Thanks for the reply .

I have tried before to debug like that but the date is in the correct pattern. i.e. dd/mm/yyyy
When I F8 through the code vpEnddate shows 03/10/2016 but the value in the field shows it as "03/02/2016" (not this would be the amended date)

Interesting point is if I change the year or the day it seems then no issue it is when I change the month????

Thoughts and many thanks

 

[dmt32]

The clue to your problem is likely to be this:

When I amend the date held in TxbAllocBEDate.Value I keep getting a type mismatch error 13 on the following lines:

As you amend the date, the change event triggers & your textbox no longer has a valid date DateValue can recognise hence the error.

without working through all your code you code trying doing something like this:

   With TxbAllocBEDate
        If IsDate(.Value) Then
            vpEndDate = DateValue(.Value)
        End If
    End With

Or as you know what the error is, you could put an On Error Resume Next statement at top of your code but not recommended.

Dave

 

[F0RE5T]

Hi many thanks and have tried isdate. Yes is true on year and day change but not on month change.

Wondering if there is a format that could be done or just overwrite the date back with original??

thoughts?????

Again many thanks especially on a sunday

Forest

 

[F0RE5T]

Dave,

I have used your suggestion as below. The issue I get is that I can change the day or year parts and the process works great. However if I change the month it gives the error message I have coded?

strange thing is if I change the month to certain months (either 01, 03, 05,07,08,10 or 12) it seem to be ok but other months I get the error message,????

Ideas?

Cheers
Forest

Private Sub TxbAllocBEDate_Change()
    Dim vFYHolidays As Range
    Dim vEntitleDeductDays As Integer
    Dim vAllocdaysOrig As Integer

   ' If vpOptions = True Then GoTo MyExit
    If Len(TxbAllocBEDate.Value) = 10 Then
        
        Set vFYHolidays = Worksheets("SysData").Range("V7:V16")
        
        vEntitleDeductDays = 0
             
        If TxbAllocDays.Value > 0 Then vAllocdaysOrig = TxbAllocDays.Value
        If IsDate(TxbAllocBEDate.Value) Then
            If TxbAllocBEDate.Value > " " Then
                vpStartDate = DateValue(TxbAllocBSDate.Value)   'global variable declared as date
                vpEndDate = DateValue(TxbAllocBEDate.Value)     'global variable declared as date
                
                If vpStartDate = vpEndDate Then
                    vEntitleDeductDays = 1
                    GoTo CheckDates
                Else
    'to calculate remaining entittlement
                    vEntitleDeductDays = vEntitleDeductDays + CDec(WorksheetFunction.NetworkDays(vpStartDate, vpEndDate, vFYHolidays))
                    If vEntitleDeductDays <= 0 Then
                        vpResponse = MsgBox("Allocation End Date before Start Date!", vbInformation, "Project Allocaion Date Error")
                        TxbAllocBEDate.Value = Format(TxbAllocBEDateOrig.Value, "dd/mm/yyyy")
                        TxbAllocBEDate.SetFocus
                        GoTo MyExit
                    Else
CheckDates:
             'total days for calendar on saving
                        vpDecimal = CDec(DateDiff("d", vpStartDate, vpEndDate))   ' + 1)
            'below changes netdays!!!!!
                        If OptbAllocNA = True Then
                            TxbAllocDays = vEntitleDeductDays
                            TxbAllocTotDays = vpDecimal 'this box is not visible TxbAllocDays
                        Else
                            TxbAllocDays = vEntitleDeductDays - 0.5 '
                            TxbAllocTotDays = vpDecimal - 0.5  'this box is not visible TxbAllocDays
                        End If
                        vpStartDate = DateValue(TxbAllocBEDate.Value)
                        vpEndDate = DateValue(TxbAllocBEDateOrig.Value)
                        If vpStartDate > vpEndDate Then
                            vpResponse = MsgBox("Allocation 'End Date' is post to Project End Date" & _
                            vbCr & "Is this authorised", vbYesNo, "Late Allocation Date")
                            If vpResponse = 7 Then
                                TxbAllocBEDate.Value = Format(vpEndDate, "dd/mm/yyyy")    '
                                TxbAllocDays.Value = vAllocdaysOrig
                                GoTo MyExit
                            End If
                        End If
                    End If
                End If
            End If
        Else
            MsgBox ("There is a error in the date format," & _
            vbCr & "Date reset will now be undertaked")
            TxbAllocBEDate.Value = Format(DateValue(TxbAllocBEDateOrig.Value), "dd/mm/yyyy")    'DateValue(TxbAllocBEDateOrig.Value)
            TxbAllocBEDate.SetFocus
            'GoTo MyExit
        End If
        
    End If
MyExit:
vpOptions = False
End Sub

 

[dmt32]

You have a lot going on in the change event & don’t have time to study all your logic

Can only suggest try couple of “fixes”

At top of your code after declarations try:

- If Not IsDate(TxbAllocBEDate.Text) Then Exit Sub

Or as you know why error occurs

- On Error GoTo MyExit

Dave

 

[F0RE5T]

You have a lot going on in the change event & don’t have time to study all your logic

Can only suggest try couple of “fixes”

At top of your code after declarations try:

- If Not IsDate(TxbAllocBEDate.Text) Then Exit Sub

Or as you know why error occurs

- On Error GoTo MyExit

Dave

Dave,

Thanks for the quick reply and I appreciate your time to look at this.
My code does what you suggest to bypass the error message but the problem is:

It does not allow the changing of the date as the error occurs so leaves sub

or

it only happens when I amend the month element and then only on particular months ???>@:


Cheers
Forest

 

[dmt32]

If you can place copy of your workbook in a dropbox & provide link to it here will if find time take a look for you or perhaps another here can assist.

Dave

 

[F0RE5T]

If you can place copy of your workbook in a dropbox & provide link to it here will if find time take a look for you or perhaps another here can assist.

Dave

Dave

Many thanks for reply but it is multi workbooks due to the nature of what I am trying to do overall.

I agree with your thoughts on how date is being returned just cannot understand why it is errored on edit of some months only.

I am rejigging the code again as the process does work when checking the activity start date.

All I am ensuring is that the new end date is not after the original end date unless authorised and is not before the start date...

Cheers anyway and be happy

Forest

P.S. used to live in Hampshire and trying to get a role back that way.