Avoid Worksheet Open Code

[Ark68]

I have an application in one workbook (wb1) that draws on data in another workbook (wb2). I have to open wb2 to access it. wb2 has a workbook open event, and when wb1 opens wb2, this code is executed and causes all kinds of issues.

Is there a way that I can disable the workbook open event when wb1 opens it?

I had thought of putting some bypass flay in the wb2 open code to skip the procedure:
eg.

Private Sub Workbook_Open() 'in wb2 - data source
    If ref2 = "wb1" Then Exit Sub 'if this workbook was opened by wb1 then bypass this workbook open code
End sub

Then in wb1, before opening wb2 adding this...

ref2 = "wb1"

But this won't work will it? I can't carry variable between two workbooks can I?

 

[jolivanes]

This is how I check which workbooks are open. Should be able to adapt that.

Sub Get_Open_WBs_Names()
    Dim j As Long, i As Long, lr As Long
    i = Application.Workbooks.Count
    If i = 2 Then MsgBox "No other WB's open beside this one and Personal.xlsb.": Exit Sub
    For j = 1 To Workbooks.Count
    lr = IIf(WorksheetFunction.CountA(Columns(3)) = 0, 1, Cells(Rows.Count, 3).End(xlUp).Row + 1)
        If Workbooks(j).Name <> ThisWorkbook.Name And Workbooks(j).Name <> "PERSONAL.XLSB" Then Cells(lr, 3).Value = Workbooks(j).Name
    Next j
End Sub

 

[Ark68]

Hi Jolivanes, thank yopu so much for sharing your knowledge. I'm not sure this is what I was looking for unfortunately. I'm not looking to determine if workbooks are open. I need to open a workbook (that iosn't already), but that workbook has a workbook open routine which I want to avoid. Basically, in this process I want to open a workbook without executing its workbook open procedure.

The workbook open procedure is critical when that workbook is opened as itself, but causes problems when opened by another workbook procedure.

 

[jolivanes]

After responding I thought that what I gave you might indeed not be what you need.
However, if it is always from the same Workbook you could use it with something like

 If Workbooks(j).Name = "Your wb1 Name here" Then Exit Sub

If the workbook names change, that won't work of course.
Sorry about the confusion

 

[CephasOz]

If you paste the following code into a normal module in wb2:

' Is the nominated workbook open?
Function WorkbookIsOpen(strWbk As String) As Boolean
    WorkbookIsOpen = False
    On Error GoTo Err_Exit
    WorkbookIsOpen = (Workbooks(strWbk).Name <> vbNullString)
Housekeeping:
    Exit Function
Err_Exit:
    Err.Clear
    Resume Housekeeping
End Function

then add a line of code at the top of the wb2's Workbook_Open code which says:

If WorkbookIsOpen("wb1.xlsm") Then Exit Sub

you should have the desired result.

 

[jolivanes]

Just thinking out loud here.
Have a Workbook named "Check_Me.xlsm" in the root directory. (Or wherever)
In your code in wb1, have a few lines that open that workbook and put the name of your ActiveWorkbook/ThisWorkbook in Cell A1.
In the code in wb2, before that code does anything else, you can check if a Workbook with the name of Cell A1 is open.
If it is, Exit Sub, Else continue on your merry way.
Or is my thinking way off?

 

[RoryA]

Disable events using application.enableevents = false, open the workbook, then set it back to True.

 

[Ark68]

Ahhhh ... I think it's finally coming to light folks. I wasn't clueing into the logic.
I am adding additional lines to the workbook open procedure (wb2) of my reference workbook that will check to see if "that" particular calling workbook (wb1) is open. If wb2 is open, it must cave called the opening of wb2 so do not execute the rest of the workbook open event. I was struggling with trying to find a solution that having wb2 tell wb1 not to run the workbook open code. But this logic has wb2 checking itself. This was Jolivane's logic in post #4 (although unsure what the 'j' is ... but I can adapt.)

I'll let you know how things go!

 

[Ark68]

LOL ... or as Rory suggested.
Thanks all for if not a solution, an opportunity to think outside the box.