CODEWITHSUNDEEP
excelvba
JavierZM
JavierZM

Reputation: 11

How to finish a sub in VBA?

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

Upvotes: 1

Views: 99

Answers (3)

VBasic2008
VBasic2008

Reputation: 55073

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 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

Upvotes: 0

Storax
Storax

Reputation: 12207

My suggestion would be to deactivate the events when opening the workbook in question

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

Upvotes: 1

JavierZM
JavierZM

Reputation: 11

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

Upvotes: 0

Related Questions