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