VBA Stops after opening file

[Shaun]

Hi Guys,

I am having an issue where the macro runs but once it opens another workbook, it just stops at

Workbooks.Open OpenFile

Once the second workbook has been opened, the active workbook

 WBName

is the workbook I would like active.

I initially had this in a worksheet to run when a cell changed and I experienced this issue. I then moved it to a module to be executed via a button but the same issue.

I am unsure how to proceed, any thoughts?

The full macro:

Sub ()

Dim File As Workbook
Dim OpenFile As String
Dim WBName As String

WBName = ActiveWorkbook.name
OpenFile = Application.ActiveWorkbook.Path & "\Data\" & ActiveSheet.Range("$B$1").Value & ".xlsx"

Application.ScreenUpdating = False


If Workbooks.Count > 1 Then
    
    For Each File In Application.Workbooks
    
        If Not (File Is Application.ActiveWorkbook) Then
            File.Save
            File.Close
            Workbooks.Open OpenFile

        End If

    Next
    
Else
    
    Workbooks.Open OpenFile

End If

Windows(WBName).Activate

Application.ScreenUpdating = True

End Sub

Cheers,

Shaun

 

[vletm]

Shaun
Could You send a real code?
eg Your the first line Sub () cannot 'work' at all.
There is missing identifier.
( it would be better to send a sample Excel-file, which has Your code too. )

 

[Shaun]

Hi vletm,

Thank you for your reply.

I have prepared a working example to demonstrate the issue.

Once

 Workbooks.open OpenFile

executes it does not return back to Chandoo Analysis.xlsm as the active workbook, the code appears to pause.

If I manually make Chandoo Analysis.xlsm the code will complete when I continue to press F8 in the VBA editor.

Cheers,

Shaun.

 

[Marc L]

Hi,​

​

according to your attachment delete the uggly Module1 then replace the Change event VBA procedure within the Sheet1 worksheet module :​

Private Sub Worksheet_Change(ByVal Target As Range)
        Dim Wb As Workbook, F$
        If Target.Address <> "$B$1" Then Exit Sub
    For Each Wb In Workbooks
      If Not Wb Is ThisWorkbook Then Wb.Close True
    Next
           F = ThisWorkbook.Path & "\" & Target.Text & ".xlsx"
    If Dir(F) > "" Then
        Application.ScreenUpdating = False
        Workbooks.Open F
        ThisWorkbook.Activate
        Application.ScreenUpdating = True
    Else
        Beep
    End If
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[Shaun]

Hi Marc L

Thank you for the reply.

The ugly module has been removed and your script pasted in the Sheet1 worksheet module.

For some reason, the file being opened remains the active workbook despite

 ThisWorkbook.Activate

I am not sure if this may affect things, but I am using Excel Version 2202 (Build 14931.20120 Click-to-Run)

Any thoughts?

Cheers,

Shaun.

 

[Marc L]

So weird as it well works on my side then the evil in on your Excel version !​

As ThisWorkbook is relative to the workbook container of the VBA procedure aka Chandoo Analysis.xlsm …​

​

To try :​

• Move the codeline ThisWorkbook.Activate after Application.ScreenUpdating = True
  
  
• Insert a codeline DoEvents before or after the codeline ThisWorkbook.Activate …