Input Box Error Handler
Help Please! I need an error handler code for this macro. I've optioned out several attempts to solve this problem. This code works, but if the Cancel button is clicked is wants to make a copy of the "CategoryCopy" sheet. I need the code to exit the sub if the Cancel button is pressed or if there is a duplicate sheet name entered.
Thanks in advance for your help!
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")
'Dim sht As Worksheet, rFound As Range
'On Error Resume Next
'For Each sht In Sheets
'Set rFound = ThisWorkbook.Worksheets("CategoryCopy").Range("D4" ).Find(sht.name)
'If Not rFound Is Nothing Then
'MsgBox " OOPS!" & vbNewLine & vbNewLine & " THAT WORKSHEET NAME ALREADY EXISTS!" & vbNewLine & vbNewLine & "Either delete the existing worksheet that has the name you are trying to use, or choose another name for the new worksheet." & vbNewLine & vbNewLine & "To delete the existing worksheet simply right click on it's sheet tab and select ""Delete"" from the pop up action menu. You will be prompted to confirm your deletion.", vbCritical, " TAX TOOL EXPRESS"
'End If
'Exit Sub
'If name = Worksheets("CategoryCopy").Range("D4") Then Exit Sub
Worksheets("CategoryCopy").Range("D4") = name
'If name = Worksheets("CategoryCopy").Range("D4") Then Exit Sub
Worksheets("CategoryCopy").Range("D7:D257").Clear
'ErrorHandler: MsgBox " OOPS!" & vbNewLine & vbNewLine & " THAT WORKSHEET NAME ALREADY EXISTS!" & vbNewLine & vbNewLine & "Either delete the existing worksheet that has the name you are trying to use, or choose another name for the new worksheet." & vbNewLine & vbNewLine & "To delete the existing worksheet simply right click on it's sheet tab and select ""Delete"" from the pop up action menu. You will be prompted to confirm your deletion.", vbCritical, " TAX TOOL EXPRESS"
'Exit Sub
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
'Call Copy_Paste_StatisticsReport
'On Error GoTo ErrorHandler
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
'ErrorHandler: MsgBox " OOPS!" & vbNewLine & vbNewLine & " THAT WORKSHEET NAME ALREADY EXISTS!" & vbNewLine & vbNewLine & "Either delete the existing worksheet that has the name you are trying to use, or choose another name for the new worksheet." & vbNewLine & vbNewLine & "To delete the existing worksheet simply right click on it's sheet tab and select ""Delete"" from the pop up action menu. You will be prompted to confirm your deletion.", vbCritical, " TAX TOOL EXPRESS"
'On Error Resume Next
'Exit Sub
'Application.DisplayAlerts = False
'Sheets("False").Visible = xlSheetVeryHidden
End Sub
|