+ Reply to Thread
Results 1 to 2 of 2

If Error on 'Type Mismatch'

Hybrid View

  1. #1
    Registered User
    Join Date
    10-26-2013
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    11

    If Error on 'Type Mismatch'

    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

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,645

    Re: If Error on 'Type Mismatch'

    You can use IsError to check if the cell contains an error, if it does then display your message, if it doesn't put the value in the textbox.

    For example, for RefundFee.
    If IsError (ssheet.Cells(nr, 16)) Then
        MsgBox "Please enter all required information."
        Exit Sub
    Else
        RefundFee.Value = ssheet.Cells(nr, 16).Value
    End If
    If posting code please use code tags, see here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Run Type error 13 Type Mismatch
    By Affan Khan in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 11-13-2012, 12:58 PM
  2. [SOLVED] Run-type error 13 type mismatch
    By misop in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-05-2012, 04:08 AM
  3. Complile Error: Type Mismatch ??? After adding error trap
    By clemsoncooz in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-22-2012, 03:50 PM
  4. Conditional Formatting - Run Time Error '13' Type Mismatch Error
    By ksp in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-17-2011, 07:37 PM
  5. [SOLVED] Help: Compile error: type mismatch: array or user defined type expected
    By lvcha.gouqizi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-31-2005, 05:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1