VBA - Run Time 91 error

[jbellon19]

Hello All,

Posted is my code:

______________________________________________________________________

Private Sub UserForm_Initialize()


key = InputBox("Which Line is this for?", "Line # ?, please enter ONLY a number")

If key = "1" Then
ElseIf key = "2" Then
ElseIf key = "3" Then
ElseIf key = "4" Then
ElseIf key = "5" Then
ElseIf key = "6" Then
ElseIf key = "7" Then
ElseIf key = "8" Then
ElseIf key = "9" Then
ElseIf key = "10" Then
ElseIf key = "11" Then
ElseIf key = "12" Then
Else
Unload Me
MsgBox ("You must enter a number, as indicated")
Exit Sub
End If

End Sub

________________________________________________________________

I have this running as a userform initializes so that I can capture a variable that I use to inject information back into the document (in more than one place).

For the life of me, I can not figure out what I have wrong. Any help would be appreciated!

Thank you

 

[Fluff]

As an alternative, you could do this in a standard module

Sub ShowForm()
    Key = InputBox("Which Line is this for?", "Line # ?, please enter ONLY a number")
    Select Case CLng(Key)
        Case 1 To 12
            UserForm1.Show
        Case Else
            MsgBox ("You must enter a number, as indicated")
    End Select
End Sub

 

[Joe4]

You are welcome!

Yes, using Case makes much more sense, but for beginners I feel that it is a bit harder to understand. I guess I can do it both ways within the code so that readers can begin get comfortable with it.

Best to teach them from the start!

Personally, I typically find nested IF statements much harder to follow than most Case statements.
Yours is pretty simple and straightforward, but if you had instances where you could have multiple different things being done based on the value, it can be pretty messy to read and follow.

And I am a big fan of anything that requires less typing (for me, that means less chance for typos!)

 

[Fluff]

And I am a big fan of anything that requires less typing (for me, that means less chance for typos!)

Couldn't agree more. Hence my variation on the select case in post#11

 

[Joe4]

Case 1 To 12

Couldn't agree more. Hence my variation on the select case in post#11

That was my first thought, but the issue is that will accept decimals, and they may not want to do that. Especially if they are supposed to be selecting row numbers!

So in order to use your suggestion, I think a bit more is needed, such as:

- Change the variable declaration like this:

Public key As Integer

- Add error handling to handle invalid entries.

 

[Fluff]

That was my first thought, but the issue is that will accept decimals, and they may not want to do that. Especially if they are supposed to be selecting row numbers!

Good point, well made.