Excel Macro for user input, loop back for user to re-input if incorrect

Question

Good day

I am trying to take a user input and update ranges on a sheet based on that, but im getting stuck on asking the user to "re-enter" his input in case it does not match any of the predefined entries.

below is my code

Sub testing_input_in_formula()

Dim wbk1 As Workbook
Dim strName As String

test1 = "C:\Users\hayekn\Desktop\book1.xlsx"

Set wbk1 = Workbooks.Open(test1)

With wbk1.Sheets("Sheet1")

On Error Resume Next

    Application.DisplayAlerts = True

    strName = InputBox(Prompt:="Enter the week you would like to update", _
    Title:="Week Selection.", Default:="Week 1")

    If strName = "Your Name here" Or _
    strName = vbNullString Then

Exit Sub

    Else

    Select Case strName

    Case "Week 1"
    .Range("A10") = "Week 1"

    Case "Week 2"
    .Range("B10") = "Week 2"

    Case Else
    MsgBox "Incorrect Entry."
   'I want it here to loop back to the "Select Case strName", 
   'where user is prompted to re-enter the text

    End Select

    End If

End With

End Sub

score 2 · Accepted Answer · edited May 23 '17 at 11:44

You could use some line-tags and Goto to jump between lines as follows:

Sub testing_input_in_formula()

Dim wbk1 As Workbook
Dim strName As String
Dim test1 As String

test1 = "C:\Users\hayekn\Desktop\book1.xlsx"
Set wbk1 = Workbooks.Open(test1)
'On Error Resume Next <- Why do you need this?
Application.DisplayAlerts = True

Re-Enter:

strName = InputBox(Prompt:="Enter the week you would like to update", _
Title:="Week Selection.", Default:="Week 1")

If strName = "Your Name here" Or strName = vbNullString Then 
    Goto The_End
Else
    With wbk1.Sheets("Sheet1")
        Select Case strName
            Case "Week 1"
                .Range("A10") = "Week 1"
            Case "Week 2"
                .Range("B10") = "Week 2"
            Case Else
                MsgBox "Incorrect Entry."
                ' if you want the user to re enter the text you should loop to the InputBox
                Goto Re-Enter
        End Select
    End With
End If

The_End:
Set wbk1 = Nothing

End Sub

Notice that I rearranged some code, added the test1 variable and set the worksheet to Nothing. Those are good habits you will need in the future :) Please give a look here to understand the meaning of the "set-to-nothing"

Awesome !! works like a charm :) And thanks for the improvements. I'm a beginner and don't know how to use best practices yet. Thanks again !! — Nadz, Jul 17 '14 at 07:18

Excel Macro for user input, loop back for user to re-input if incorrect

1 Answers1