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
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
Code:
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
Last edited by a moderator: