Error Trapping an input box - where to start ?

netrixuser

New Member
Joined
Jan 21, 2019
Messages
27
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
Rich (BB code):
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.
VBA Code:
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
VBA Code:
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.
VBA Code:
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 !!
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
How about
Create a new private variable before the code
VBA Code:
   Dim RegionFail As Boolean
And use
VBA Code:
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
VBA Code:
   If RegionFail Then Exit Sub
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,293
A different approach might be using a function instead of a sub procedure, and check for the return value of the function.

VBA Code:
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:
VBA Code:
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
 
Solution

eirikdaude

Board Regular
Joined
Nov 26, 2013
Messages
57
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

New Member
Joined
Jan 21, 2019
Messages
27

ADVERTISEMENT

How about
Create a new private variable before the code
VBA Code:
   Dim RegionFail As Boolean
And use
VBA Code:
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
VBA Code:
   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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

netrixuser

New Member
Joined
Jan 21, 2019
Messages
27

ADVERTISEMENT

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

VBA Code:
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:
VBA Code:
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

BatCoder
Joined
Feb 28, 2002
Messages
1,293
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,242
Messages
5,600,506
Members
414,385
Latest member
Lioness227

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top