IF statement and inputbox check validate user input

Question

I have a code that asks the user to select a sheet by writing its name in an inputbox, and then I need to check if the selected name is correct.

How can I write the "if" statement so to return back to the inputbox?

I'm using MS Word in Windows 7. This is the code:

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

---

Sub OpenExcelFile()

    Dim oExcel As Excel.Application
    Dim oWB As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim oneRange As Excel.Range
    Dim aCell As Excel.Range
    Dim intChoice As Integer
    Dim strPath As String
    Dim uiSheet As String

    Set oExcel = New Excel.Application

    'Select the start folder
    Application.FileDialog(msoFileDialogOpen _
    ).InitialFileName = ActiveDocument.path
    'Remove all other filters
    Call Application.FileDialog(msoFileDialogOpen).Filters.Clear
    'Add a custom filter
    Call Application.FileDialog(msoFileDialogOpen).Filters.Add( _
    "Only Excel File Allowed", "*.xl*")
    'only allow the user to select one file
    Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
    'make the file dialog visible to the user
    intChoice = Application.FileDialog(msoFileDialogOpen).Show
    'determine what choice the user made
    If intChoice <> 0 Then
        'get the file path selected by the user
        strPath = Application.FileDialog( _
            msoFileDialogOpen).SelectedItems(1)
    End If
    'open excel file and select sheet
    Set oWB = oExcel.Workbooks.Open(strPath)
    Dim strBuild As String
    'set Array for user input control
    Dim myArray() As Variant
    ReDim myArray(1 To oWB.Sheets.Count)

    'populate input box and array
    For Each xlSheet In oWB.Worksheets
        strBuild = strBuild & xlSheet.Name & vbCrLf
        For i = 1 To oWB.Sheets.Count
            myArray(i) = oWB.Sheets(i).Name
        Next i
    Next xlSheet

    'show inputbox with list of sheets
    strBuild = Left$(strBuild, Len(strBuild) - 2)

    uiSheet = InputBox("Provide a sheet name." & vbNewLine & strBuild)

    'check if User input match with sheet name
    If IsInArray(uiSheet, myArray) Then
        'show excel window
        oExcel.Visible = True

        'sort selected sheet by first column range
        oExcel.Worksheets(uiSheet).Activate
        Set oneRange = oExcel.Range("A1:A150")
        Set aCell = oExcel.Range("A1")
        oneRange.Sort Key1:=aCell, Order1:=xlAscending, Header:=xlYes
    Else
        MsgBox "Please enter a valid name!", vbCritical
    End If

End Sub

Answer 1

If you replace the code starting with uiSheet = InputBox(..... with the code below, it should work.

'check if User input match with sheet name
Dim bSheetPresent As Boolean
bSheetPresent = False

Do Until bSheetPresent
    uiSheet = InputBox("Provide a sheet name." & vbNewLine & strBuild)
    If uiSheet = "" Then Exit Do
    If IsInArray(uiSheet, myArray) Then
        bSheetPresent = True
    Else
        MsgBox "Please enter a valid name!", vbCritical
    End If
Loop

If bSheetPresent Then
    'show excel window
    oExcel.visible = True

    'sort selected sheet by first column range
    oExcel.Worksheets(uiSheet).Activate
    Set oneRange = oExcel.Range("A1:A150")
    Set aCell = oExcel.Range("A1")
    oneRange.Sort Key1:=aCell, Order1:=xlAscending, Header:=xlYes
End If

If the user presses cancel on the inputbox, the it will exit the loop.

You may also consider to build a form with a pre-filled combobox. That way the user cannot make a mistake.