Add In writes VBA at runtime to new workbook. How do I reference the add in?

Hi Forum Members,

TL/DR: How do I reference a worksheet from an add-in in the workbook_open code of a workbook created at run time?


I have an add in that creates a new workbook and, in addition to creating a report, writes code to object modules (both the sheets and the ThisWorkbook Workbook_Open event).

I copy a worksheet from the add in, called Permissions, that sets the parameters for the user (what they can access and what they can update on the new file). One field that I reference is basically a show/hide of some sensitive data.

I have the worksheet set to very hidden and the users are not really sophisticated with VBA so that *should* suffice.

However, I would like to further protect this data by *not* importing the permissions sheet. I would like to keep it in the add in and then reference it from the workbook_open event.

Using pseudo-code,

instead of:
boolSensitiveData = ActiveWorkbook.Sheets(Permissions.Name).Cells(1,1)

replace with:
boolSensitiveData = AddIn.Sheets(Permissions.Name).Cells(1,1)

This will dynamically capture user security/permission changes in addition to providing an extra layer of security.

How do I reference the add in in the workbook_open code?

The user sets the path of the entire system at creation time, but the add in can be in a different location. I would prefer not to maintain a separate file and restricting access, but will do so. That would entail opening the permissions file with the _open event, get what is needed, and then closing the file. I do that with the data, but want to keep some stuff secret.

Use Thisworkbook, which is a reference to the workbook containing the code being executed.