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]

If you are moving from the Midlands to Hampshire then I should probably let dmt32 take over.

Anyway, plan B. One of RickXL's rules of programming is if something appears to defy logic than replace it with something that should not make any difference and see how it copes.

The answer may be in the parts of your solution that you have not posted, like how you declare the dates and how you set up the TextBoxes. I have assumed that vpStartDate and vpEndDate are defined as Dates. I have tried other definitions but I cannot replicate your error.

So try this and see what happens. It is reformatted slightly but that is due to OCD on my part.

I have added a section that splits the date entered into day month and year, displays it in a MsgBox and processes it using DateSerial instead of DateValue.

Please let me know what happens.

'Private vpStartDate  As Date
'Private vpEndDate    As Date

Private Sub TxbAllocBEDate_Change()
    Dim vFYHolidays         As Range
    Dim vEntitleDeductDays  As Integer
    Dim vAllocdaysOrig      As Integer
    Dim vpResponse          As Variant
    Dim vpDecimal
    Dim OptbAllocNA         As Boolean
    Dim TxbAllocTotDays     As Long
    Dim vpOptions           As Boolean
    
    ' 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
            
            Dim dd As String, mm As String, yyyy As String
            dd = Left(TxbAllocBEDate.Value, 2)
            mm = Mid(TxbAllocBEDate.Value, 4, 2)
            yyyy = Right(TxbAllocBEDate.Value, 4)
            MsgBox dd & vbLf & mm & vbLf & yyyy
            vpEndDate = DateSerial(yyyy, mm, dd)
            
            '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