CODEWITHSUNDEEP
vbaexcelruntime-errorexcel-2010
RoseDeSang
RoseDeSang

Reputation: 21

Run-time error '13': Type Mismatch

I am trying to create an excel sheet in which when I select a drop down list option an input box shows up and asks the user to enter a number. This is to create a "skill planner" for a game I play. So I am using the code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Select Case Range("A4")

Case "Endurance"
Call Module1.GetEndurance
Case "Active Regeneration"
Call Module2.GetActiveRegen

End Select

End Sub

in ThisWorkbook and inside Module1 is

Sub GetEndurance()
Dim QtyEntry As Integer
Dim MSG As String
Const MinSkill As Integer = 0
Const MaxSkill As Integer = 100
MSG = "Please enter skill level between " & MinSkill & " and " & MaxSkill
Do
    QtyEntry = InputBox(MSG)
    If IsNumeric(QtyEntry) Then
        If QtyEntry >= MinSkill And QtyEntry <= MaxSkill Then Exit Do
        End If
            MSG = "... Really? I told you the valid options..."
            MSG = MSG & vbNewLine
            MSG = MSG & "Please enter skill level between " & MinSkill & " and " & MaxSkill
            Loop
Sheet2.Range("B2").Value = QtyEntry

End Sub

Module2 has the exact same code except it's called GetActiveRegen() and it goes to Sheet2.Range("B3").Value = QtyEntry.

When I select one of these two drop down options that input box shows up and I can put in a number, it puts that number where it's suppose to, the problem I have is that it keeps asking me to put in a number, if I hit cancel I get the error 13 message, if I put in no answer and click ok then it gives me the error 13 message. This is my first time doing programming in excel VBA and I have no real programming experience so this is getting highly frustrating. Any help will be greatly appreciated.

The error is showing on this line:

 QtyEntry = InputBox(MSG)

Upvotes: 1

Views: 12367

Answers (1)

Jan Rothkegel
Jan Rothkegel

Reputation: 762

You've chosen the wrong datatype for QtyEntry in the declaration. Choose string instead of integer.

Sub GetEndurance()
Dim QtyEntry As String
Dim MSG As String
Const MinSkill As Integer = 0
Const MaxSkill As Integer = 100
MSG = "Please enter skill level between " & MinSkill & " and " & MaxSkill
Do
    QtyEntry = InputBox(MSG)
    If QtyEntry = "" Then Exit Do
    If IsNumeric(QtyEntry) Then
        If QtyEntry >= MinSkill And QtyEntry <= MaxSkill Then Exit Do
        End If
            MSG = "... Really? I told you the valid options..."
            MSG = MSG & vbNewLine
            MSG = MSG & "Please enter skill level between " & MinSkill & " and " & MaxSkill
            Loop
Sheet2.Range("B2").Value = QtyEntry

End Sub

Upvotes: 1

Related Questions