Why is it giving me a mismatch error?

Question

When the user is prompted to give a survery of my excel document, I have them enter in a number between 1 and 10. The Sub Test is to eliminate Strings and numbers outside of 1 through 10. This sub works. If the user enters a number 1 through 10 the first time, the program works like a charm. However, there is an error that pops up if the user enters a string first and then puts in an integer from 1-10. It is a run-time error 13 type mismatch. I have commented out the line that pops up as an error. However, the email goes through still. Also, if you can explain why I have to redefine my RANK integer, that would also help. Option Explicit is not working like I read about in my book. Thank you! If you have any questions, please let me know!!

Option Explicit
Private Sub CommandButton1_Click()
Dim OutApp As Object
Dim OutMail As Object
Dim RANK As Long


Call Test

RANK = UserForm1.TextBox1.Value  '((((THIS IS THE LINE THAT PROMPTS THE ERROR)))
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = "(my email is inserted here)"
.CC = ""
.BCC = ""
.Subject = "Create Database Overall Ranking"
.Body = "The rank a company gave me was a " & RANK & "."

.send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

Unload UserForm1

End Sub

Sub Test()
Dim MyVar, MyCheck
Dim RANK As Long
 MyVar = UserForm1.TextBox1.Text ' Assign value.
 MyCheck = IsNumeric(MyVar) ' Returns True.

If MyCheck = True Then
RANK = UserForm1.TextBox1.Value

If RANK > 10 Or RANK < 1 Then

MsgBox "Please input a number between 1 and 10.", vbOKOnly, "Invalid Number"
UserForm1.TextBox1.Text = ""
On Error Resume Next
Unload UserForm1
UserForm1.Show
Else
End If
Else
MsgBox "Please input a number and not text.", vbOKOnly, "Invalid Input"
UserForm1.TextBox1.Text = ""
On Error Resume Next
Unload UserForm1
UserForm1.Show
End If
End Sub

EDIT: THE CODE THAT WORKS!!!

I got it to work....here is the code. I just had the test to make sure that it is a number and i ran the number as a string.

Private Sub CommandButton1_Click()
Dim OutApp As Object
Dim OutMail As Object
Dim SurveyNum As String


Call Test

SurveyNum = UserForm1.TextBox1.Text

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)


On Error Resume Next
With OutMail
.To = "(MY EMAIL HERE)"
.CC = ""
.BCC = ""
.Subject = "Create Database Overall Ranking"
.Body = "The rank a company gave me was a " & SurveyNum & "."

.send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

Unload UserForm1

End Sub
Sub Test()
Dim MyVar, MyCheck
Dim SurveyNum2 As Double
 MyVar = UserForm1.TextBox1.Text ' Assign value.
 MyCheck = IsNumeric(MyVar) ' Returns True.

If MyCheck = True Then
SurveyNum2 = UserForm1.TextBox1.Value

If SurveyNum2 > 10 Or SurveyNum2 < 1 Then

MsgBox "Please input a number between 1 and 10.", vbOKOnly, "Invalid Number"
UserForm1.TextBox1.Text = ""
On Error Resume Next
Unload UserForm1
UserForm1.Show
Else
End If
Else
MsgBox "Please input a number and not text.", vbOKOnly, "Invalid Input"
UserForm1.TextBox1.Text = ""
On Error Resume Next
Unload UserForm1
UserForm1.Show
End If
End Sub

when you set OutMail set it to a MailItem not just CreateItem(0). like Set OutMail = New OUtlook.MailItem — Sorceri
use the debugger and tell us which line is causing the issue — Sorceri
did you enter in a string first...then inserted a integer the second time and it didn't give you an error? — user2916633

guitarthrower guitarthrower · Accepted Answer · 2014-01-03T17:50:04

There are a few things going on that may be causing an issue.

Rank is a function that already exists in VBA. You may be running into problems using this same name for something user-defined. Although I don't think that is what is giving you the error.

I think the problem lies with declaring RANK as long. The long data type, as I understand it, is an integer. If the value you are passing to this data type is not an integer, it could be the culprit.

I would rename the rank variable and redefine it as double and see what that does for you.

EDIT with code

I managed to fix the code. I don't know exactly what was causing the issue but there are a few items I changed.

You declared separate variables in the same procedure for the same textbook value, but were using different data types (RANK as Long and myVar as Variant in the Test procedure)
I also cleaned up a few items to make the code flow a bit better.

Here's the code.

Option Explicit

Private Sub CommandButton1_Click()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim SurveyNum As Double

    If Test = True Then

        SurveyNum = UserForm1.TextBox1.Value
        Set OutApp = CreateObject("Outlook.Application")
        OutApp.Session.Logon
        Set OutMail = OutApp.CreateItem(0)

        On Error Resume Next
        With OutMail
            .To = "(my email is inserted here)"
            .CC = ""
            .BCC = ""
            .Subject = "Create Database Overall Ranking"
            .Body = "The rank a company gave me was a " & SurveyNum & "."

            .send
        End With
        On Error GoTo 0

        Set OutMail = Nothing
        Set OutApp = Nothing

        Unload UserForm1
    End If
End Sub

Function Test() As Boolean
    Dim MyVar As Double
    Dim MyCheck As Boolean

    MyVar = UserForm1.TextBox1.Value ' Assign value.
    MyCheck = IsNumeric(MyVar) ' Returns True.

    If MyCheck = True Then

        If MyVar > 10 Or MyVar < 1 Then

            MsgBox "Please input a number between 1 and 10.", vbOKOnly, "Invalid Number"
            UserForm1.TextBox1.Value = ""
            On Error Resume Next
            Test = False
        Else
            Test = True
        End If
    Else
        MsgBox "Please input a number and not text.", vbOKOnly, "Invalid Input"
        UserForm1.TextBox1.Value = ""
        On Error Resume Next
        Test = False
    End If
End Function

Why is it giving me a mismatch error?

1 Answers