CODEWITHSUNDEEP
vbaexcel
Seb
Seb

Reputation: 520

Open workbook with arguments

Question : Is there a way to open a workbook and pass an argument/parameter through this action ?

The idea is to differentiate the case where a user directly open Workbook B (which will trigger its Auto_Open code) and the case where Workbook B is opened from the code of an other Workbook.

Upvotes: 1

Views: 6730

Answers (1)

Rory
Rory

Reputation: 34045

The two options I suggested, in brief:

  1. In the workbook you are opening, add this to the ThisWorkbook module:

Public OpenedInCode as Boolean

Your calling workbook needs to open the workbook, then use something like

With Workbooks("workbook name.xlsm")
   .OpenedInCode = True
   .RunAutoMacros xlAutoOpen
End With

Then the Auto_Open code needs to check this value. If it's False, the user opened it; if True, your other workbook opened it.

Option 2 is simply to create another routine with the code from your Auto_Open that takes an optional, say, Boolean parameter. Your Auto_Open code then just calls this routine. Your calling workbook would use Run

Dim wb as Workbook
Set wb = Workbooks.Open(path to file here)
Application.Run "'" & wb.Name & "'!routine_name", True

As with option 1, the called routine just checks to see if the argument is True or False and reacts accordingly.

Upvotes: 4

Related Questions