Search code examples
vbaexceluserform

VBA prevent closing of UserForm with "End" in code


In my spreadsheet, I have a UserForm that is supposed to be open at all times.

Once in a while, my code will contain an "End" where I exit the code based on some if statement.

The problem is that this closes the UserForm, is there a way to prevent this from happening?

EDIT:

Sub Test1()
    'Random code       
    Call Test2(Variable)
    'Random code           
End Sub

Sub Test2(ByVal Variable as Double)
    If Variable = 0 then
        'Random code
    End If
    If Variable = 1 then
        Call Test3
        End 'Original placement of End 
    End If
End Sub

Sub Test3()
    'Random code
End Sub

This is a rough example of how the code is build (its rather long at this point). So depending on the "variable" different things happen in Test2. But if the Variable is 1, then the "random code" back in Test1 can't be executed thus, so I have to stop the code. I tried replace "End" with "Exit Sub" this only stops the code in Test2 from running, is it will give me an error when it goes back to Test1.

EDIT2: Test1() is actually four different subs (at this point, more will be added) that all call Test2(). That is why I choose to split it up into so many subs and call them from within the subs.


Solution

  • You somehow need to tell Test1 that it needs to stop. One approach to this problem is to change your subs to functions and return a value indicating status. Something like this would work:

    Function Test1() As Integer
        Dim i As Integer
    
        'Random code
        i = Test2(Variable)
        If i = 1 Then Exit Function
        'Random code
    End Function
    
    Function Test2(ByVal Variable As Double) As Integer
        Test2 = 0
    
        If Variable = 0 Then
            'Random code
        End If
        If Variable = 1 Then
            Call Test3
            Test2 = 1
            Exit Function
        End If
    End Function
    
    Function Test3() As Integer
        'Random code
    End Function