VBA End Private Sub if First Sub Routine is exited

Question

I want the entire Private Sub to Exit if the Copier routine is exited. So the DoDays routines is never called.

Sub Copier()
Dim x As String
Dim z As Integer
x = InputBox("Enter Number of Days in Month")
If x = "" Then
     MsgBox "User Pressed Cancel!" & vbCrLf & _
            "or did not enter a value!", vbOKOnly + vbInformation, _
            "Inputbox Result:"
     z = 10
     Exit Sub
ElseIf CInt(x) = 0 Then
     MsgBox "User Pressed Cancel!" & vbCrLf & _
            "or did not enter a value!", vbOKOnly + vbInformation, _
            "Inputbox Result:"
     z = 10
     Exit Sub
Else: End If
y = CInt(x) - 1
For numtimes = 1 To y
ActiveWorkbook.Sheets("Sheet1").Copy _
after:=ActiveWorkbook.Sheets("Sheet1")
Next

DoDays

End Sub



Private Sub COPY_NUMBER_Click()
COPY_NUMBER.BackColor = 12713921
Copier
' DoDays
COPY_NUMBER.BackColor = 12500670
COPY_NUMBER.Enabled = False
End Sub

The call to the DoDays in the Copier sub doesn't seem to work because I literally need to exit the Private Sub so the button remains enabled.

Answer 1

I would merge the Copier procedure into the COPY_NUMBER_Click event procedure:

Private Sub COPY_NUMBER_Click()
    COPY_NUMBER.BackColor = 12713921

    Dim x As String
    x = InputBox("Enter Number of Days in Month")
    If x = "" Then
         MsgBox "User Pressed Cancel!" & vbCrLf & _
                "or did not enter a value!", vbOKOnly + vbInformation, _
                "Inputbox Result:"
         Exit Sub
    ElseIf CInt(x) = 0 Then
         MsgBox "User Pressed Cancel!" & vbCrLf & _
                "or did not enter a value!", vbOKOnly + vbInformation, _
                "Inputbox Result:"
         Exit Sub
    End If
    y = CInt(x) - 1
    For numtimes = 1 To y
        ActiveWorkbook.Sheets("Sheet1").Copy _
                     After:=ActiveWorkbook.Sheets("Sheet1")
    Next

    DoDays

    COPY_NUMBER.BackColor = 12500670
    COPY_NUMBER.Enabled = False
End Sub

Answer 2

Create a global variable and update it at the end of your Copier method then check it before DoDays is called

Private bRunDoDays As Boolean
Sub Copier()
'set to false
bRunDoDays = False
Dim x As String
Dim z As Integer
x = InputBox("Enter Number of Days in Month")
If x = "" Then
     MsgBox "User Pressed Cancel!" & vbCrLf & _
            "or did not enter a value!", vbOKOnly + vbInformation, _
            "Inputbox Result:"
     z = 10
     Exit Sub
ElseIf CInt(x) = 0 Then
     MsgBox "User Pressed Cancel!" & vbCrLf & _
            "or did not enter a value!", vbOKOnly + vbInformation, _
            "Inputbox Result:"
     z = 10
     Exit Sub
Else: End If
y = CInt(x) - 1
For numtimes = 1 To y
ActiveWorkbook.Sheets("Sheet1").Copy _
after:=ActiveWorkbook.Sheets("Sheet1")
Next
'set to true
bRunDoDays = True

End Sub



Private Sub COPY_NUMBER_Click()
COPY_NUMBER.BackColor = 12713921
Copier
If bRunDoDays = False Then Exit Sub
DoDays
COPY_NUMBER.BackColor = 12500670
COPY_NUMBER.Enabled = False
End Sub

Answer 3

You can change Copier to a Boolean Function and edit the call to test whether it executed successfully.

Your call would look like:

If Not Copier Then Exit Sub

Your Copier Function would look like:

Public Function Copier() As Boolean   
  'Does Stuff
  Copier = True
End Function

Make sure you have Option Explicit enabled. It should have thrown a compile error on the If z = 10 Then Exit Sub since it is out of scope.