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

jdjehanneman

New Member
Joined
Mar 23, 2021
Messages
16
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Try
VBA Code:
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
 
Upvote 0
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 Auto_Open sub that first checks if a text file exists and then reads the sting in it to store in the variable TemplatesPath. Perhaps the Auo_Open is causing the problem?

Dim TemplatesPath As String
Dim FileNamePath As String

Sub Auto_Open()
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
 
Upvote 0
Try with added 2 code lines
VBA Code:
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
 
Upvote 0
Finally got it to work by hiding the userform before opening the second file:

VBA Code:
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
 
Upvote 0
Solution

Forum statistics

Threads
1,223,243
Messages
6,170,971
Members
452,371
Latest member
Frana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top