How to finish a sub in VBA?-CodePudding

I Have a Sub Who opens a new workbook, but this new workbook has its Sub that immediately Activates a user form, and the first Sub never ends, so this is my question How Can I finish the first sub?

first Workbook

Private Sub BotonBalanza_Click()
 Workbooks.Open Filename:="C:\Users\proc_221\Desktop\Balanza.xlsm", Password:="genesis1969"
End Sub

Second Workbook

Private Sub Workbook_Open()
Application.Visible = False                
Seleccion.Show
End Sub

Thank you

CodePudding user response：

In Another Instance of Excel

This will run your destination open workbook code only if the application instance is visible.
It will open the destination workbook in another invisible instance and do the job, ensuring the instance gets closed properly and informing of success.

Destination ThisWorkbook Module

Option Explicit

Private Sub Workbook_Open()
    If Application.Visible = True Then
        Application.Visible = False
        Seleccion.Show
    End If
End Sub

Source 'wherever the button is' Sheet Module

Option Explicit

Private Sub BotonBalanza_Click()

    Const ProcName As String = "BotonBalanza"
    
    Dim ErrNum As Long
    
    On Error GoTo ClearError
    
    Dim xlApp As Application: Set xlApp = New Application
    Dim wb As Workbook: Set wb = xlApp.Workbooks.Open( _
            Filename:="C:\Users\proc_221\Desktop\Balanza.xlsm", _
            'Password:="genesis1969")
    
    ' do your stuff, e.g.:
    Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet1")
    Dim rg As Range: Set rg = ws.Range("A1")
    rg.Value = Now
    rg.EntireColumn.AutoFit
        
SafeExit:
        
    On Error Resume Next
    If ErrNum = 0 Then
        If Not wb Is Nothing Then
            wb.Close SaveChanges:=True
        End If
        xlApp.Quit
        MsgBox "Success", vbInformation
    Else
        If Not wb Is Nothing Then
            wb.Close SaveChanges:=False
        End If
        xlApp.Quit
        MsgBox "Failed.", vbCritical
    End If
    On Error GoTo 0
    
    Exit Sub
ClearError:
    ErrNum = Err.Number
    Debug.Print "'" & ProcName & "' Run-time error '" _
        & Err.Number & "':" & vbLf & "    " & Err.Description
    Resume SafeExit
End Sub

CodePudding user response：

Thanks for all; I fixed the error using a delay time on the code.

Sub Mostrar()
 Workbooks.Open Filename:="C:\Users\proc_221\Desktop\Balanza.xlsm", Password:="genesis1969"
End Sub

Private Sub BotonBalanza_Click()
Application.OnTime Now   TimeValue("00:00:03"), "Mostrar"
End Sub

Private Sub Workbook_Open()
Seleccion.Show 
End Sub