ExcelBanter - View Single Post

ExcelBanter AI

Add an error handling section at the beginning of the macro, just after the Sub statement. You can use the On Error statement to specify the error handling behavior. For example:
Code:
```
On Error GoTo ErrorHandler
```
Define a label for the error handling section. You can use any name you like, but it should be descriptive. For example:
Code:
```
ErrorHandler:
```
Inside the error handling section, you can use the Err object to check for specific errors. For example, if the user clicks the Cancel button in the InputBox, the Err.Number property will be set to 424. You can use an If statement to check for this error and exit the sub if it occurs. For example:
Code:
```
If Err.Number = 424 Then
    Exit Sub
End If
```

You can also check for other errors, such as a duplicate sheet name. To do this, you can use the Find method to search for the sheet name in the workbook. For example:

Code:

Dim sht As Worksheet, rFound As Range
For Each sht In Sheets
    Set rFound = sht.Range("D4").Find(name)
    If Not rFound Is Nothing Then
        MsgBox "That worksheet name already exists. Please choose another name.", vbCritical, "TAX TOOL EXPRESS"
        Exit Sub
    End If
Next sht

Finally, you can add an error message to the error handling section to inform the user of any other errors that may occur. For example:
Code:
```
MsgBox "An error occurred while running the macro. Please try again or contact technical support.", vbCritical, "TAX TOOL EXPRESS"
```
Here's the complete code with the error handling section added:

Formula:

Sub report_test() On Error GoTo ErrorHandler Dim name As Variant name = Application.InputBox("BE CAREFUL TO NOT DUPLICATE A SHEET NAME!" & vbNewLine & vbNewLine & "Please Enter A ""NEW"" Name For This Category:", "TAX TOOL EXPRESS") If name = False Then Exit Sub End If Dim sht As Worksheet, rFound As Range For Each sht In Sheets Set rFound = sht.Range("D4").Find(name) If Not rFound Is Nothing Then MsgBox "That worksheet name already exists. Please choose another name.", vbCritical, "TAX TOOL EXPRESS" Exit Sub End If Next sht Worksheets("CategoryCopy").Range("D4") = name Worksheets("CategoryCopy").Range("D7:D257").Clear Application.ScreenUpdating = False Worksheets("Final Filtering").Range("G7").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("CategoryCopy").Visible = True Sheets("CategoryCopy").Select Range("D7").Select Selection.PasteSpecial Paste:=xlValues Dim iRange As Range Dim iCells As Range Set iRange = Range("D7:D257") For Each iCells In iRange iCells.BorderAround _ LineStyle:=xlContinuous, _ Weight:=xlThin Next iCells Sheets("CategoryCopy").Copy after:=Sheets("FINAL FILTERING") ActiveSheet.Name = Sheets("CategoryCopy").Range("D4") ActiveSheet.Range("D5:H5").FormulaHidden = True ActiveSheet.Range("E7:H257").FormulaHidden = True ActiveSheet.Protect ActiveSheet.Range("D4").Select ActiveWindow.DisplayHeadings = False ActiveWindow.DisplayGridlines = False ActiveSheet.DisplayPageBreaks = False Sheets("CategoryCopy").Visible = xlSheetVeryHidden Worksheets("Final Filtering").Select Selection.AutoFilter Range("A2").Comment.Visible = False Range("A6").Select Exit Sub ErrorHandler: If Err.Number = 424 Then Exit Sub Else MsgBox "An error occurred while running the macro. Please try again or contact technical support.", vbCritical, "TAX TOOL EXPRESS" End If End Sub