Passing a boolean value to the Workbook_Open event

[RawlinsCross]

Good day,

I have two spreadsheets with the first opening the second via a pretty standard method (Application.FileDialog(msoFileDialogFilePicker))

Spreadsheet #1

Set fd = Application.FileDialog(msoFileDialogFilePicker)
bExit_ = False

With fd
    .Filters.Clear
    .ButtonName = "Load Incident"
    .Filters.Add "Excel Files", "*.xls?, *.csv", 1
    .Title = "Choose your incident report"
    .AllowMultiSelect = False
    .InitialFileName = sPath
    If .Show = True Then
        For i = 1 To .SelectedItems.Count
            sFile = .SelectedItems(i)
           
            Set mWb = Workbooks.Open(sFile)
        Next i
    Else
        bExit_ = True
        Exit Sub
    End If
End With

sFile represents the 2nd spreadsheet. On the 2nd spreadsheet, there is small Workbook_Open routine where a variable bAdmin_ is set to False by default. I'd like this to be false when someone opens up the 2nd spreadsheet directly but set to true if the 1st spreadsheet opens up the 2nd.

Spreadsheet #2

Public Sub Workbook_Open()

bAdmin_ = False
Call WorkbookSetup

ThisWorkbook.Worksheets("Form").Activate
ThisWorkbook.Worksheets("Form").Range("C20").Select

End Sub

Any thoughts?

 

[RoryA]

You can't pass arguments to that event. I'd suggest you use a separate routine that is called from Workbook_Open and takes an optional argument (defaulting to False). Your other code can then disable events and call that routine using application.run

 

[RawlinsCross]

Hi Rory - not sure how 'disable events' and 'application.run' play out in this scenario.

 

[RoryA]

You disable events using:

application.enableevents = false

then open the workbook and run its macro using:

application.run “‘“ & mwb.name & “‘!macro_name”, true

 

[RawlinsCross]

How handy is that! Works like a charm. Thanks for the tips.