VBA Excel - after launching a xlsm file VBA still running doesn't exit sub

[jdjehanneman]

Hello,

Using the code below, I want to open an xlsm file aside of the workbook from which I am executing the code.
The problem is that after opening the xlsm, the code keeps running and the macros in the xlsm cannot be executed. I have to exit manually the sub in the visual basic editor in order to be able to execute the macro's.

Private Sub bt_OpenManager_Click()
Dim strTemplateFilePath As String
Dim wkb As Excel.Workbook

If listbox_Paths_Managers.ListIndex <> -1 Then
strTemplateFilePath = PATH_CICTOOLS_ADMIN & listbox_Paths_Managers.List(listbox_Paths_Managers.ListIndex)
Set wkb = Workbooks.Open(strTemplateFilePath)
Exit Sub
End If
Exit Sub
End Sub

Can someone help me, I would really appreciate it.

Johan

 

[mohadin]

Try

Private Sub bt_OpenManager_Click()
    Dim strTemplateFilePath As String
    Dim wkb As Excel.Workbook
    If listbox_Paths_Managers.ListIndex <> -1 Then
        strTemplateFilePath = PATH_CICTOOLS_ADMIN & listbox_Paths_Managers.List(listbox_Paths_Managers.ListIndex)
        Set wkb = Workbooks.Open(strTemplateFilePath)
    End If
End Sub

 

[jdjehanneman]

Hi Mohadin,

It's not fixing the problem. The vba is stuck in the sub, it doesn't exit it.
The .xlsm file I'm opening has an Autpen sub that first checks if a text file exists and then reads the sting in it to store in the variable TemplatesPath. Perhaps the Aupen is causing the problem?

Dim TemplatesPath As String
Dim FileNamePath As String

Sub Autpen()
Dim WorkbookName As String
Dim FileNamePath As String
Dim TemplatesPath As String

' Get the path where to save the templates -> from management txt file specific for the manager
Globals_DeclareVars
FileNamePath = GetFileNamePath()
If Functions_CheckFileExists(FileNamePath) = True Then
TemplatesPath = functions_readfile(FileNamePath)
Else
TemplatesPath = "None"
End If
End Sub

 

[mohadin]

Try with added 2 code lines

Private Sub bt_OpenManager_Click()
    Dim strTemplateFilePath As String
    Dim wkb As Excel.Workbook
    Application.EnableEvents = False
    If listbox_Paths_Managers.ListIndex <> -1 Then
        strTemplateFilePath = PATH_CICTOOLS_ADMIN & listbox_Paths_Managers.List(listbox_Paths_Managers.ListIndex)
        Set wkb = Workbooks.Open(strTemplateFilePath)
    End If
    Application.EnableEvents = True
End Sub

 

[jdjehanneman]

Finally got it to work by hiding the userform before opening the second file:

Private Sub bt_OpenManager_Click()
    Dim strTemplateFilePath As String
    Dim wb As Excel.Workbook

    If listbox_Paths_Managers.ListIndex <> -1 Then
        strTemplateFilePath = PATH_CICTOOLS_ADMIN & listbox_Paths_Managers.List(listbox_Paths_Managers.ListIndex)
        FrmTemplateManager.Hide
        Set wkb = Workbooks.Open(strTemplateFilePath)
    End If
End Sub