I have the following script that allows the user to add a new sheet within the excel workbook, however if the user hits ESC or clicks CANCEL, the msgbox works and the EXIT SUB works, however, when the user clicks OK without entering any characters to name the sheets, the EXIT SUB works but the message doesn’t appear??
Section of the below code that I cannot get to function correctly starts at
If APRef = False Or Len(APRef) = 0 Then
WPCheck = MsgBox("No WP reference entered, process cancelled", vbCritical)
Exit Sub
Code
Sub AddWPApd()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim wk As String
Dim APRef As String
Dim APDsc As String
Dim APResult As Integer
APResult = 0
wk = ActiveSheet.Name
On Error GoTo ExitWPAddition
If ActiveSheet.Index <= 14 Then
Error = MsgBox("You are not permitted to add a WP in this section, it is only possible to add working papers from Sheets " & """HC010""" & " onwards.", vbExclamation)
Else
APRef = UCase(Application.InputBox(Prompt:="Please enter WP Appendix Ref, eg HC010.a1 for Turnover Appendices.", Title:="WP Appendices Ref", Type:=2))
If APRef = False Or Len(APRef) = 0 Then
WPCheck = MsgBox("No WP reference entered, process cancelled", vbCritical)
Exit Sub
Else
For k = 1 To Sheets.Count
If Sheets(k).Index > 14 Then
If APRef = Sheets(k).Name Then
If vbYes = MsgBox("Sheet already exists, do you want to go to that WP?", vbYesNo) Then Sheets(APRef).Select
APResult = APResult + 1
Exit For
End If
End If
Next k
If APResult = 0 Then
APDsc = Application.InputBox(Prompt:="Please enter WP Description, eg Top 20 Customers CY 'v' PY.", Title:="WP Appendices Description", Type:=2)
Sheets("Add WP").Visible = True
Sheets("Add WP").Copy After:=ActiveSheet
ActiveSheet.Name = UCase(APRef)
ActiveSheet.Range("D6") = StrConv(APDsc, vbProperCase)
Sheets("Add WP").Visible = False
Dim ShCount As Integer, i As Integer, j As Integer
ShCount = Sheets.Count
For i = 1 To ShCount - 1
For j = i + 1 To ShCount
If Sheets(i).Index > 14 Then
If UCase(Sheets(j).Name) < UCase(Sheets(i).Name) Then
Sheets(j).Move before:=Sheets(i)
End If
End If
Next j
Next i
Call Sort
End If
Sheets(APRef).Select
End If
End If
ExitWPAddition:
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
If the user hits ESC, CANCEL, or clicks OK without entering any characters to name the new sheet, the message box should display error message then exit the sub, however, it only displays when hitting ESC or CANCEL, not the OK, but all options still EXIT SUB
Tayque Holmes is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Input Box Vs Error Handler
Input Box
-
You are trying to compare a Boolean with a String which would raise
Run-time error '13': Type mismatchbut your poor error handling hides the error. -
You could have used
If APRef = "False" Or Len(APRef) = 0 Thenwhich I have seen often but consider inaccurate. -
Also, don’t forget to restore the settings that you have turned off at the beginning of the code when exiting.
-
IMO, the way to go is the following:
Dim APRef As Variant, IsInputValid As Boolean APRef = UCase(Application.InputBox( _ Prompt:="Please enter WP Appendix Ref, eg HC010.a1 for Turnover Appendices.", _ Title:="WP Appendices Ref", Type:=2)) If Vartype(APRef) <> vbBoolean Then ' user pressed 'OK' If Len(APRef) <> 0 Then ' user entered at least one character IsInputValid = True 'Else ' user pressed 'OK' but entered nothing End If 'Else ' user pressed 'Cancel' End If If Not IsInputValid Then MsgBox "No WP reference entered, process cancelled", _ vbExclamation ' it's not the end of the World Goto ProcExit ' to restore the settings that you turned off End If -
Either implement a Boolean (see above) or place an appropriate message box in each Else statement. That is what the Application.InputBox method is for, to distinguish between these two cases. If you don’t need this functionallity, use the InputBox Function instead and simply check for the length of the input.
Error Handling
-
Handle errors more appropriately, e.g.:
ProcExit: Application.DisplayAlerts = True Application.ScreenUpdating = True Exit Sub ExitWPAddition: MsgBox "Run-time error [" & Err.Number & "]:" & vbLf & vbLf _ & Err.Description, vbCritical Resume ProcExit End Sub