Date Field error?

Hi,
Wondering if anybody can perhaps help me with an issue I have:
I have a userform which is used by users to enter data into a spreadsheet. This userform contains various textboxes and combo boxes, it also contains 2 command buttons (linked to a calendar control) to populate 2 textboxes with date values. Finally, the userform contains a command button to populate various cells on the spreadsheet, using the data entered into the fields mentioned above.

Once the values have been entered into the spreadsheet and the userform closed, I use the worksheet selectionchange event to do various calculations on the spreadsheet.

The problem I have is:
When fields on the userform are not populated with any values and entered into the spreadsheet, when the selection change event fires I get the error 'runtime error 13, type mismatch'
However, when the fields on the userform are populated and entered into the spreadsheet the selectionchange event code works perfectly.

I have an idea that the issue is with the date fields, not being happy with the null value assigned to it when the userform field is empty.
I would really appreciate some help/info/guidance..

Thanks
Ben

Could you display the SelectionChange code, and the line at which the error occurs.

- Mangesh

The code I have is:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Dim intRunBalance1 As Integer
Dim intRunBalance2 As Integer

With ActiveSheet

intRunBalance1 = .Range("E5") - .Range("D7")
.Range("E7") = intRunBalance1
intRunBalance2 = .Range("E7") - .Range("D8")
.Range("E8") = intRunBalance2


End With
End Sub

The error occurs at the highlighted line above.
Cells E5 and D7 are both populated with integer values by the userform.

You mean to say that the cell contains a non-numeric value like NULL. If that is the case, your SelectionChange code will give you that error. What you could do to avoid this error is: You could place a line
On Error Resume Next
just after the line
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
in your code.

Another way is to keep the cell blank when you don't submit any values through the userform.

- Mangesh

Thank You Mangesh, that works, your help is very much appreciated!

Please note:
If you are using the On Error Resume Next approach, your error has not disappeared, but the code overlooks it. And in doing this, your code would overlook any other error as well.

- Mangesh

Thank you, I am aware of that
Can you give me some idea of how to keep the cell blank when you don't submit any values through the userform?

-Ben

Actually, when you don't populate the textboxes in the userform and press the submit button, then the cells should remain blank. I don't know why you are getting that error. But maybe if you post the code for the CommandButton1_Click() event, maybe I could help in finding why you are getting the error.

- Mangesh

My code for the submit button on the userform is attached, once the cells have populated with the values of the userform fields, I set them to the value of "" to clear the form.
--------------------------------------


Private Sub CommandButton1_Click()

Select Case cmbLine.Value

Case "1" 'This is the Line Number combo box on the user form
With ActiveSheet
.Range("b7").Value = txtDateF 'Populates Cell b7 with the vaule of txtDateF
.Range("c7").Value = txtDateT
.Range("d7").Value = txtTotal
.Range("h7").Value = cmbReason
.Range("i7").Value = txtComments
End With
txtDateF.Text = "" 'Clears the useform bt setting the values of all fields to ""
txtDateT.Text = ""
txtTotal.Text = ""
cmbReason.Value = ""
txtComments.Text = ""

Case "2"
With ActiveSheet
.Range("b8").Value = txtDateF
.Range("c8").Value = txtDateT
.Range("d8").Value = txtTotal
.Range("h8").Value = cmbReason
.Range("i8").Value = txtComments
End With
txtDateF.Text = ""
txtDateT.Text = ""
txtTotal.Text = ""
cmbReason.Value = ""
txtComments.Text = ""
Case "3"
With ActiveSheet
.Range("b9").Value = txtDateF
.Range("c9").Value = txtDateT
.Range("d9").Value = txtTotal
.Range("h9").Value = cmbReason
.Range("i9").Value = txtComments
End With
txtDateF.Text = ""
txtDateT.Text = ""
txtTotal.Text = ""
cmbReason.Value = ""
txtComments.Text = ""
End Select
End Sub

Hi Benjammind,

You get the type mismatch error on the line:
intRunBalance1 = .Range("E5") - .Range("D7")
because, either one of them or both are empty. So modify your Worksheet_SelectionChange code as follows:

Please Login or Register  to view this content.

- Mangesh

Hi Mangesh,

Thank you for your reply, unfortunately I still receive the same runtime error. At this line in the code below: intRunBalance1 = .Range("E5") - .Range("D7")
--------------------------------------------------------

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim intRunBalance1 As Integer
Dim intRunBalance2 As Integer

With ActiveSheet
If Not (IsEmpty(.Range("E5")) And IsEmpty(.Range("D7"))) Then
intRunBalance1 = .Range("E5") - .Range("D7")
.Range("E7") = intRunBalance1
End If

If Not (IsEmpty(.Range("E5")) And IsEmpty(.Range("D7"))) Then
intRunBalance2 = .Range("E5") - .Range("D8")
.Range("E8") = intRunBalance2
End If

End With

Hi Benjammind,

I am very sorry. Please replace the AND with OR in both the cases. That should solve your problem.

- Mangesh