CODEWITHSUNDEEP
excelvba
CptGoodar
CptGoodar

Reputation: 303

Way to not use goto loops in vba msgbox

I am trying to find a way to avoid using GoTo loops in VBA as i understand they can lead to serious confusion and issues. I have a user InputBox, where the user defines a variables, currently on the if statement there are 3 options, if = Y ElseIf = N and else GoTo Start.

However this works great for the case where the user mistypes the variable i.e Y# etc, but i currently run into issues when the user wants to close the input box i.e clicks cancel or the cross.

So i was wondering if there is a more elligent solution to this or am I stuck with this hicup?

My code is below, this is only a test set used to test this new feature i am adding to my main code. 

Sub MsgBox_Test ()

Dim TestVariable As String

VariableEntrey: TestVariable = InputBox(" Y or N")
If TestVariable = "Y" Or TestVariable = "y" Then
    MsgBox ("Yeyy")
ElseIf TestVariable = "N" Or TestVariable = "n" Then
    MsgBox ("Awww")
Else: GoTo VariableEntrey
End If 

End Sub

Thanks for any help you are able to provide

Upvotes: 2

Views: 724

Answers (5)

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

You could try with Do .... Loop here:
Edit: plus limitation for single character or empty input

Dim TestVariable As String
TestVariable = InputBox(" Y or N")

Do While (TestVariable = "N" Or TestVariable = "n" or Len(TestVariable) > 1)
    MsgBox ("Awww")
    TestVariable = InputBox(" Y or N")

Loop 

If TestVariable = "Y" Or TestVariable = "y" Then
    MsgBox ("Yeyy")
End if

Upvotes: 0

jsotola
jsotola

Reputation: 2278

Option Explicit

Sub MsgBox_Test()

    Dim TestVariable As String
    Dim done As Boolean

    Do
        TestVariable = InputBox(" Y or N")

        done = True            ' preload exit value

        If LCase(TestVariable) = "y" Then
            MsgBox ("Yeyy")

        ElseIf LCase(TestVariable) = "n" Then
            MsgBox ("Awww")

        ElseIf Len(TestVariable) > 0 Then

            done = False       ' abort exit

        End If

    Loop Until done

End Sub

Upvotes: 1

Shai Rado
Shai Rado

Reputation: 33682

You could use Do >> Loop Until loop, to remove the GoTo.

Also, you can use UCase(TestVariable) to remove the Or from your Ifs.

Sub MsgBox_Test()

Dim TestVariable As String

Do
    TestVariable = InputBox(" Y or N")
    If UCase(TestVariable) = "Y" Then
        MsgBox ("Yeyy")
    ElseIf UCase(TestVariable) = "N" Then
        MsgBox ("Awww")
    End If
Loop Until UCase(TestVariable) Like "[YN]"

End Sub

Upvotes: 0

CallumDA
CallumDA

Reputation: 12113

I would use a MsgBox instead of an InputBox.

Sub GetYesNo()
    Dim answer As VbMsgBoxResult

    answer = MsgBox("Yes or No?", vbYesNo)

    If answer = vbYes Then
        'do something
    Else
        'do something else
    End If
End Sub

enter image description here

Upvotes: 0

Robin Mackenzie
Robin Mackenzie

Reputation: 19319

You can try a Do... Loop Until construct that just repeats until you get an acceptable answer. For example:

Sub GetAnswer()

    Dim strAnswer As String

    Do
        strAnswer = InputBox("Y or N")
    Loop Until strAnswer Like "[YyNn]"

    MsgBox "Thanks for your answer of: " & strAnswer

End Sub

See the docs on the Like operator to prevent having to check individually for y, Y, n and N.

Upvotes: 3

Related Questions