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

 

[Octofez2]

Exit Sub should come after End If. The code is stopping before finishing the If statement, which more than likely is causing the error.

 

[Norie]

How/where have you declared key?

 

[Joe4]

Exit Sub should come after End If. The code is stopping before finishing the If statement, which more than likely is causing the error.

That is not true at all.
It is quite common to do it this way. Sometimes you want the code to exit the sub in the True or False part of an If statement.
It is perfectly fine to structure the code that way.

Which line of code is returning the error?

 

[jbellon19]

How/where have you declared key?

Good Morning!

______________________
Option Explicit


Public key As String
Public product As String
______________________

Those 2 variables have been declared at the top of the userform, prior to any Subs

 

[jbellon19]

That is not true at all.
It is quite common to do it this way. Sometimes you want the code to exit the sub in the True or False part of an If statement.
It is perfectly fine to structure the code that way.

Which line of code is returning the error?

It actually doesn't specify a line, and it doesn't open up debug mode. It seems to operate as it should, but then just pushes a msgbox to me that says:

"Run-time error '91':
Object variable or With block variable not set"

 

[Fluff]

I'm pretty sure that you can't have

Unload Me

In an Initialize sub.
As the form has not been loaded, you can't unload it

 

[jbellon19]

I'm pretty sure that you can't have

Unload Me

In an Initialize sub.
As the form has not been loaded, you can't unload it

Well, that makes sense!

Is there anyway for me to prevent the form to load then if the condition is not met?

 

[Joe4]

Take a look here: https://www.ozgrid.com/forum/forum/...oad-userform-in-the-initialize-event?t=150698

Also, you can use a Case statement instead of a real messy IF statement, i.e.

    Select Case key
        Case "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12"
            'Do stuff here
        Case Else
            'Do stuff here
    End Select

 

[jbellon19]

Take a look here: https://www.ozgrid.com/forum/forum/...oad-userform-in-the-initialize-event?t=150698

Also, you can use a Case statement instead of a real messy IF statement, i.e.

    Select Case key
        Case "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12"
            'Do stuff here
        Case Else
            'Do stuff here
    End Select

Perfect, thank you Joe4!!

This makes sense, and I love the idea of a boolean value, because I am going to have to do this a lot.

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.

Thank you very much!