Error Trapping an input box - where to start ?

[netrixuser]

Hello, a relative newbie here - trying hard to learn....
I have worked out error trapping for a message box, but for some reason am struggling with an input box.
Main code below calling smaller sub routines

Dim region As String
Dim YesErr As Boolean
Sub Get_Reports()
    Call DeleteTabs
    Call OpenFiles
        If YesErr = True Then Exit Sub
    Call RegionSelect1
    Call RenameTabs
    Call AccsysColumnsZandAA
    Call AccsysAddColumns
    Call AccsysOutlineAllCells
    Call AccsysFormatRow1
    Call AccsysInsertUsenameCol
    Call AccsysFlipUsername
    Call AccsysHideColumns
    Call AccsysCapitalLetters
    Call AccsysCheckColumn
    Call RegionSelect2
    Call HighlightOlderThan30
    Call VoxSmartTidyDates
    Call CogniaTidyDates
    Call AccsysTidySheet1
    Call VoxSmartTidySheet
    Call VoxSmartOutlineAllCells
    Call CogniaOutlineAllCells
   'Call SaveAs
    MsgBox "File saved as: 'MMM-YYYY Monthly MRL Reconciliation [Region]' in the Documents folder" & vbNewLine _
        & "Note, Macros have been removed from this document", , "Reconciliation Spreadsheet ready for use "
End Sub

DeleteTabs does just that, checks if any tabs were saved in the workbook that shouldn't be there and deletes them.

Sub DeleteTabs()
        Dim xWs As Worksheet
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
            For Each xWs In Application.ActiveWorkbook.Worksheets
                If LCase(xWs.Name) <> "index" Then xWs.Delete
            Next
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
End Sub

OpenFiles is below - I am fairly happy with this code but would like the GetOpenFilename to repeat a couple of times before exiting the sub - this isn't a show stopper though

Sub OpenFiles()
Dim controlFile As Variant
Dim TargetFiles As Variant
Dim X As Integer
Dim wb As Variant
        YesErr = False
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
            If MsgBox("Have you downloaded the latest reports for this month?", vbYesNo _
                + vbQuestion + vbDefaultButton1, "Before you begin") = vbNo Then
                    YesErr = True
                    MsgBox "Please Download The Appropriate Accsys report and 3rd Party Vendor Reports", vbExclamation, "Whoops!!"
                    Exit Sub
            End If
            controlFile = ActiveWorkbook.Name
                ChDir "C:\Users\" & Environ$("UserName") & "\Downloads"
                TargetFiles = Application.GetOpenFilename _
            (Title:="Please choose the files you wish to open", _
            FileFilter:="Excel and CSV Files (*.xls*;*.csv),*.xls*;*.csv", MultiSelect:=True)
            If Not IsArray(TargetFiles) Then
                MsgBox "Please Select The Appropriate Files", vbExclamation, "Whoops!"
                YesErr = True
            Exit Sub
                Else
                    For X = 1 To UBound(TargetFiles)
                    Set wb = Workbooks.Open(TargetFiles(X))
                    Windows(wb.Name).Activate
                    Sheets(1).Copy After:=Workbooks(controlFile).Sheets(1)
                    Windows(wb.Name).Close (False)
                Next X
            End If
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
End Sub

RegionSelect1 is the issue - a simple input box that assigns the value to the global variable "region" which is used later to call other subs depending on the value.
Regarding the Error Trapping ....
I want the sub to throw a message box if the user types anything other than AMER, EMEA or EH telling them to try again, or if they type a number to tell them that and to try again.
Also, if they press enter (or ok) which will input the default of "For Example EMEA", or press cancel, or press the X - to all throw a message box.
If they "mess up" more than twice to exit the sub.
At this point though, the three workbooks have already been opened in the OpenFiles sub above so before exiting the sub I would like the DeleteTabs sub to run again.
I realise I do not need the displayalerts and screenupdating in each sub - to be tidied up later.

Sub RegionSelect1()
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        region = UCase(InputBox("Which Regional Report Are You Creating ? AMER, EMEA or EH", "Enter Region", "For Example EMEA"))
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
End Sub

Hope that all made sense ! as I say, I'm pretty happy with the code but want to do something if users of the macro try and break things by entering rogue stuff !!

Any help gratefully received !!

 

[Fluff]

How about
Create a new private variable before the code

   Dim RegionFail As Boolean

And use

Sub RegionSelect1()
   Dim i As Long
   RegionFail = False
   Do
      Region = UCase(InputBox("Which Regional Report Are You Creating ? AMER, EMEA or EH", "Enter Region", "For Example EMEA"))
      Select Case Region
         Case "AMER", "EMEA", "EH": Exit Do
         Case Else:
            i = i + 1
            If i < 3 Then MsgBox "incorrect try again"
            RegionFail = True
      End Select
   Loop Until i = 3
End Sub

If you put this as the first macro called you don't have to worry about deleting the sheets again.
And after the call use

   If RegionFail Then Exit Sub

 

[smozgur]

A different approach might be using a function instead of a sub procedure, and check for the return value of the function.

Function RegionSelect1() As Boolean
Dim isRepeat As Boolean

' Using Retry label to repeat the selection one more time
Retry:
    region = UCase(InputBox("Which Regional Report Are You Creating ? AMER, EMEA or EH", "Enter Region", "For Example EMEA"))
    ' Exit if Cancel button is clicked
    If region = "" Then Exit Function
    
    ' Is the entered value valid?
    Select Case region
        Case "AMER", "EMEA", "EH"
            ' It is valid value, so we can return success to the caller
            RegionSelect1 = True
            Exit Function
    End Select
    
    ' Check if we asked for the region twice
    ' and exit if we did
    If isRepeat Then Exit Function
    
    ' Asked only once, try one more time
    isRepeat = True
    GoTo Retry
End Function

And the caller:

Sub main()
    ' Previous macro calls
    
    ' If function returns false, then stop processing
    If Not RegionSelect1 Then
        MsgBox "Region is not selected. I stop here.", vbOKOnly + vbExclamation, "The End"
        Exit Sub
    End If
    ' Next macro calls
End Sub

 

[eirikdaude]

If you only want to accept a few values, I'd recommend using a userform with a list- or combobox instead of an inputbox. It will probably be less work putting this together than trying to vet what's coming out of the inputbox.

 

[netrixuser]

How about
Create a new private variable before the code

   Dim RegionFail As Boolean

And use

Sub RegionSelect1()
   Dim i As Long
   RegionFail = False
   Do
      Region = UCase(InputBox("Which Regional Report Are You Creating ? AMER, EMEA or EH", "Enter Region", "For Example EMEA"))
      Select Case Region
         Case "AMER", "EMEA", "EH": Exit Do
         Case Else:
            i = i + 1
            If i < 3 Then MsgBox "incorrect try again"
            RegionFail = True
      End Select
   Loop Until i = 3
End Sub

If you put this as the first macro called you don't have to worry about deleting the sheets again.
And after the call use

   If RegionFail Then Exit Sub

Thank you for your help Fluff ! it works and it is easy (for me) to see what is happening. I would still like to tweak my code to error trap further during the message box, but I'm sure that will come with experience - I am enjoying the journey !

Thanks again - off to look at the other replies to see if I understand them too :D

 

[Fluff]

You're welcome & thanks for the feedback.

 

[netrixuser]

A different approach might be using a function instead of a sub procedure, and check for the return value of the function.

Function RegionSelect1() As Boolean
Dim isRepeat As Boolean

' Using Retry label to repeat the selection one more time
Retry:
    region = UCase(InputBox("Which Regional Report Are You Creating ? AMER, EMEA or EH", "Enter Region", "For Example EMEA"))
    ' Exit if Cancel button is clicked
    If region = "" Then Exit Function
   
    ' Is the entered value valid?
    Select Case region
        Case "AMER", "EMEA", "EH"
            ' It is valid value, so we can return success to the caller
            RegionSelect1 = True
            Exit Function
    End Select
   
    ' Check if we asked for the region twice
    ' and exit if we did
    If isRepeat Then Exit Function
   
    ' Asked only once, try one more time
    isRepeat = True
    GoTo Retry
End Function

And the caller:

Sub main()
    ' Previous macro calls
   
    ' If function returns false, then stop processing
    If Not RegionSelect1 Then
        MsgBox "Region is not selected. I stop here.", vbOKOnly + vbExclamation, "The End"
        Exit Sub
    End If
    ' Next macro calls
End Sub

Hi Smozgur, thank you for your code and the explanations. It works too (sort of) and it is again easy to follow - except that if I enter the correct region (ie AMER, EMEA or EH) the input box repeats itself before running the rest of the code

 

[smozgur]

Hi Smozgur, thank you for your code and the explanations. It works too (sort of) and it is again easy to follow - except that if I enter the correct region (ie AMER, EMEA or EH) the input box repeats itself before running the rest of the code

You're welcome.

Are you sure you are calling the function once? It works only once if I enter the correct region.

 

[smozgur]

Are you sure you are calling the function once?

I mean, you don't need Call RegionSelect1 code line anymore since If Not RegionSelect1 already calls the function.