How to determine the name of a read-only file (activeworkbook.name causes error 91)

Hi There,

I have a rather large and cumbersome program. I have two questions!

Version:
Excel 2016

The problem:
When the workbook containing my program is open (we'll call it "Program Workbook"), if I open any other file in Excel, it triggers the CALCULATE event from ThisWorkbook sheet of the "Program Workbook".

Please Login or Register  to view this content.

I regularly need to open (.xml) files from emails. When I open these files, they are opened as Read-only.

in the CALCULATE event, I use the reference

Please Login or Register  to view this content.

Trying to execute this line is producing the error message:

vba excel run-time error '91':
Object variable or With block variable not set.

I want to avoid this so that I don't need to shut down the Program Workbook every time I wish to open a read-only sheet (which is regularly!). I also don't wish to copy the file from the email to the desktop and then open it.
(I am effectively using this to ensure that the "Program Workbook" is the active workbook (by checking if activeworkbook.name = thisworkbook.name)

Please Login or Register  to view this content.

So the question is,
Can I capture the name of the read-only file that has just opened by a different method, or can I check to see if the activeworkbook is read-only to avoid the above error?
(checking

Please Login or Register  to view this content.

produces the same error).
Alternatively, can I avoid the error by a different check to protect this line from fateful execution?!

I wonder as the file is trying to open, perhaps there is no activeworkbook at that moment?


As a secondary question, I wouldn't have thought that opening a different workbook would have triggered the CALCULATE event on the "Program Workbook" ThisWorkbook sheet. Should this be the case? If not, what could be triggering it? There is nothing in "Personal.xlsb" ThisWorkbook sheet.


I would appreciate any comments that might guide me in the correct direction!



additional info (which I don't think is required, but may throw some light on the subject)

I need the CALCULATE event in The Program in order to perform an operation on one of the sheets within the workbook.). I have used volatile data (the

Please Login or Register  to view this content.

function and also formulae to trigger the CALCULATE event when anything about specific sheets are changed)

Hi xlnitwit,

Thanks for your response (and confirmation of answer to Question 2!).

Yes, when I double click on the file in Outlook, it is opening in [Protected View].
The file appears with the yellow ribbon(information bar) at the top of the workbook, "Protected View Be careful-email attachments can contain viruses. Unless you need to edit, it's safer to stay in Protected View.".
In the ribbon bar, it includes the button ("Enable Editing") which can be clicked (or not).

What is the best way to cater for this in the macro and avoid an error?

Please Login or Register  to view this content.

Is exactly what I was after - Thank you xlnitwit !!



(SOLVED)