VBA activates the wrong Workbook

[davidam]

On one workbook (A), I have a button that opens another workbook (Main.xls) and then proceeds to close the first workbook. The problem is that once it finishes closing Workbook A, if another workbook is open, it then maximizes whichever workbook it happens to feel like, and not necessarily the one I want it to, even though I have specifically activated it. Perhaps I could put a variable specifying the book I want in the BeforeClose Sub of Workbook A. (I do not always the same book to be active after closing Book A so it would have to be done with a variable.) Any suggestions would be greatly appreciated. Many thanks!

Public Sub Back_To_Main()
Application.ScreenUpdating = False
Application.EnableEvents = False
Call Open_Main 'OpenWorkbooks module
Workbooks("Main.xls").Activate
Application.ScreenUpdating = True
Application.EnableEvents = True
ThisWorkbook.Close False
End Sub

This is the code that opens the workbook that I want to be active

Public Sub Open_Main()
On Error GoTo ErrorHandler
Workbooks("Main.xls").Activate
ActiveSheet.Unprotect Password:=PW
Range("A14").Value = 0
Range("G17").Select
ActiveSheet.Protect Password:=PW
Exit Sub
ErrorHandler:
Workbooks.Open Filename:=ThisWorkbook.Path & "\Main.xls"
ActiveSheet.Unprotect Password:=PW
Range("A14").Value = 0
Range("G17").Select
ActiveSheet.Protect Password:=PW
End Sub

This is the ThisWorkbook Code that is used to prevent changes to the workbook (A) that is being closed.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  Application.MoveAfterReturnDirection = xlDown
  ActiveWorkbook.Saved = True
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 ActiveWorkbook.Saved = True
 Cancel = True
End Sub

 

[Norie]

Where are you actually running all this code?

It's pretty confusing, for me anyway.

You have code in workbook A that opend workbook 'Main.xls', then closes the 'first' workbook.

Which workbook is the 'first' workbook? Workbook A?

Why do you want/need any of the workbooks to be active?

 

[davidam]

The 'first' workbook is Workbook A. There are two total A and Main. I am going from A to Main...

 

[davidam]

PS There are a series of books in the project...I want only one to be visible and open at any one time

 

[Norie]

So which workbook is the code in?

 

[davidam]

The code is in workbook A