If Error on 'Type Mismatch'

**jimmyex23** · 12-10-2013, 11:02 AM

I am running a VBA userform that takes data input by the user and outputs a calculated value. If the user does not put in all of the required data, the calculation cannot be perfomed, and excel will return #Value instead of an actual calculated value. When my VBA userform wants to recall this calculated value to display on the userform, I get an error:

Run-time error ‘-2147352571 (80020005)’:

Could not set the Value property. Type Mismatch.

I understand why this error occurs. My vba userform is trying to pull in a value from a spreadsheet and instead it gets #Value. These types do not match. What I would like to do is if this error occurs, a pop up would occur on my userform stating "Please enter all required information." How would I execute this If Error statement? Also, these values I am displaying are called RefundFee and ProcessingFee. Thank you for your help, I do not have much experience with If Error statements.

Below is my Userform Code:

'Calendar Entry
Private Sub calSur_DateClick(ByVal DateClicked As Date)

txtSur.Value = DateClicked

End Sub
'Calendar Entry
Private Sub calExp_DateClick(ByVal DateClicked As Date)

txtExp.Value = DateClicked

End Sub

'Userform Appearances
Private Sub cboBusiness_Change()
        
Select Case Me.cboBusiness.Value
    Case "Stoop Line Stand"
        lblSLS.Visible = True
        cboSLS.Visible = True
        lblStand.Visible = True
        txtStand.Visible = True
    Case Else
        lblSLS.Visible = False
        cboSLS.Visible = False
        lblStand.Visible = False
        txtStand.Visible = False
End Select

Select Case Me.cboBusiness.Value
    Case "Bingo Game Operator", "Games of Chance"
        lblBingo1.Visible = True
        txtBingo1.Visible = True
        lblBingo2.Visible = True
        txtBingo2.Visible = True
    Case Else
        lblBingo1.Visible = False
        txtBingo1.Visible = False
        lblBingo2.Visible = False
        txtBingo2.Visible = False
End Select
            
Select Case Me.cboBusiness.Value
    Case "Tow Truck Company", "Tow Truck Exemption", "Sightseeing Bus"
        lblVehicle.Visible = True
        txtVehicle.Visible = True
    Case Else
        lblVehicle.Visible = False
        txtVehicle.Visible = False
End Select

Select Case Me.cboBusiness.Value
    Case "Pedicab Business"
        lblPedi.Visible = True
        optYes2.Visible = True
        optNo2.Visible = True
    Case Else
        lblPedi.Visible = False
        optYes2.Visible = False
        optNo2.Visible = False
End Select

Select Case Me.cboBusiness.Value
    Case "Amusement Device (Temporary)"
        lblAmTemp.Visible = True
        txtAmTemp.Visible = True
    Case Else
        lblAmTemp.Visible = False
        txtAmTemp.Visible = False
End Select


Select Case Me.cboBusiness.Value
    Case "Newsstand", "General Vendor - Veteran", "Bingo Game Operator", "Games of Chance", "Tow Truck Company", "Tow Truck Exemption", "Pedicab Business", "Sightseeing Bus", "Stoop Line Stand", "Amusement Device (Temporary)", "Special Sale", "Temporary Street Fair Vendor Permit"
        lblFee.Visible = False
        txtFee.Visible = False
    Case Else
        lblFee.Visible = True
        txtFee.Visible = True
End Select

Select Case Me.cboBusiness.Value
    Case "", "Newsstand", "Special Sale", "Temporary Street Fair Vendor Permit", "General Vendor - Veteran"
        lblQ.Visible = False
    Case Else
        lblQ.Visible = True
End Select





End Sub





Private Sub cboIssue_Change()
Select Case Me.cboIssue.Value
    Case "License Surrendered (includes TempOp, Temp C of O)"
        lblSur.Visible = True
        txtSur.Visible = True
        cmdSur.Visible = True
        lblExp.Visible = True
        txtExp.Visible = True
        cmdExp.Visible = True
    Case Else
        lblSur.Visible = False
        txtSur.Visible = False
        cmdSur.Visible = False
        lblExp.Visible = False
        txtExp.Visible = False
        cmdExp.Visible = False
End Select
End Sub

'Data Entry into Excel
Private Sub cmdCalculate_Click()

If Len(txtRecord) = 0 Then MsgBox ("Please enter a Record ID number.")

Dim ssheet As Worksheet
Set ssheet = ThisWorkbook.Sheets("Calculator")
nr = ssheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
ssheet.Cells(nr, 1) = Me.txtRecord
'ssheet.Cells(nr, 2) = Me.optYes.Value
'ssheet.Cells(nr, 3) = Me.optNo.Value
ssheet.Cells(nr, 3) = Me.cboIssue
ssheet.Cells(nr, 4) = Me.cboBusiness
ssheet.Cells(nr, 5) = Me.txtSur
ssheet.Cells(nr, 6) = Me.txtExp
ssheet.Cells(nr, 7) = Me.txtFee
ssheet.Cells(nr, 8) = Me.cboSLS
ssheet.Cells(nr, 9) = Me.txtStand
ssheet.Cells(nr, 10) = Me.txtVehicle
ssheet.Cells(nr, 11) = Me.txtAmTemp
ssheet.Cells(nr, 12) = Me.optYes2.Value
ssheet.Cells(nr, 13) = Me.optNo2.Value
ssheet.Cells(nr, 14) = Me.txtBingo1.Value
ssheet.Cells(nr, 15) = Me.txtBingo2.Value

RefundFee.Value = ssheet.Cells(nr, 16)
ProcessingFee.Value = ssheet.Cells(nr, 17)


End Sub


'Calendar Visiblity
Private Sub cmdSur_Click()

frameSur.Visible = True

End Sub
'Calendar Visiblity
Private Sub cmdExp_Click()

frameExp.Visible = True

End Sub


Private Sub Frame1_Click()

End Sub

Private Sub lblPermission_Click()

End Sub

'Date Visibility
Private Sub optYes_Click()
lblSur.Visible = True
txtSur.Visible = True
cmdSur.Visible = True
lblExp.Visible = True
txtExp.Visible = True
cmdExp.Visible = True

End Sub

'Date Visibility
Private Sub optNo_Click()
lblSur.Visible = False
txtSur.Visible = False
cmdSur.Visible = False
frameSur.Visible = False
lblExp.Visible = False
txtExp.Visible = False
cmdExp.Visible = False
frameExp.Visible = False
End Sub



'Date Format Validation
Private Sub txtSur_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
On Error Resume Next
Me.txtSur = CDate(Me.txtSur)
End Sub
'Date Format Validation
Private Sub txtExp_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
On Error Resume Next
Me.txtExp = CDate(Me.txtExp)
End Sub



'Textbox validation
Private Sub txtRoom_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

If (KeyAscii > 47 And KeyAscii < 58) Then KeyAscii = KeyAscii Else KeyAscii = 0

End Sub
'Textbox validation
Private Sub txtBingo_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

If (KeyAscii > 47 And KeyAscii < 58) Then KeyAscii = KeyAscii Else KeyAscii = 0

End Sub


'Textbox validation

Private Sub txtVehicle_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

If (KeyAscii > 47 And KeyAscii < 58) Then KeyAscii = KeyAscii Else KeyAscii = 0

End Sub
'Textbox validation
Private Sub txtStand_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

If (KeyAscii > 47 And KeyAscii < 58) Then KeyAscii = KeyAscii Else KeyAscii = 0

End Sub
'Textbox validation
Private Sub txtTable_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

If (KeyAscii > 47 And KeyAscii < 58) Then KeyAscii = KeyAscii Else KeyAscii = 0

End Sub
'Textbox validation
Private Sub txtFee_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

If (KeyAscii > 47 And KeyAscii < 58) Or KeyAscii = 46 Then KeyAscii = KeyAscii Else KeyAscii = 0

End Sub
'Textbox validation

Private Sub txtSur_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

If (KeyAscii > 46 And KeyAscii < 58) Then KeyAscii = KeyAscii Else KeyAscii = 0

End Sub
'Textbox validation
Private Sub txtExp_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

If (KeyAscii > 46 And KeyAscii < 58) Then KeyAscii = KeyAscii Else KeyAscii = 0

End Sub
'Textbox validation
Private Sub txtBingo1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

If (KeyAscii > 47 And KeyAscii < 58) Then KeyAscii = KeyAscii Else KeyAscii = 0

End Sub 'Textbox validation
Private Sub txtBingo2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

If (KeyAscii > 47 And KeyAscii < 58) Then KeyAscii = KeyAscii Else KeyAscii = 0

End Sub
Private Sub txtAmTemp_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

If (KeyAscii > 47 And KeyAscii < 58) Then KeyAscii = KeyAscii Else KeyAscii = 0

End Sub

Private Sub UserForm_Click()

End Sub