samedi 3 octobre 2015

IF statement and inputbox check validate user input

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

Aucun commentaire:

Enregistrer un commentaire