[Solved] Opening file in Read Only with VBA and code already existing

Hi all,

I have this workbook used by many people as a dashboard ; when I need to update it, I usually ask they close their files (only a few PCs), but it's going to be expanded, and that's not something I'll be able to do anymore.

So I tried to force the opening to Read Only, but it isn't handy as it requires a Yes, and I believe it will fly over the head of way too many people. For now, I'll leave it like this, but I tried to go through VBA, with this little bit :

Please Login or Register  to view this content.

As is, it works, but I have several other code running at start/open of the file, and the application of the Read Only status overrides some of them it appears, specifically the hiding of the ribbons.

I've tried working around putting the code on Activation, but it didn't work out. How could I integrate it properly ?

Please Login or Register  to view this content.

Hi there,

The following approach might do what you need.

Password-protect the master workbook (password known only to yourself, e.g. ZZZ).

Provide Users with a loader workbook which opens the master workbook in read-only mode. The loader workbook is stored in the same folder as the master workbook.

If Users try to open the master workbook directly they will be asked to insert a password (which they will not have).

The loader workbook contains the following code in the VBA CodeModule of ThisWorkbook:

Please Login or Register  to view this content.

The highlighted values may be altered to suit your requirements.


Hope this helps - please let me know how you get on.

Regards,

Greg M

Hi Greg,

it works fine ! There's a tiny typo here (should be Filename), you might want to edit your original reply ^^ :

Please Login or Register  to view this content.

I'll tinker a bit around, to auto-close the Loader file. I will eventually create more dashboards for different services, and I guess I could adapt the Open condition to different buttons instead.

Does this have to have Loader and Master in the same folder, or can it include a target folder ? It's not that important, as I can still use shortcuts to lead to the Loader, but I'm curious. (I will try on my own in the meantime ; I need to change the Me. variable)

But thanks already for your help ! This opens a whole lot of options for me !

Hi Artik,

thanks for the input ! it works, but I'm confronted with the same problem I had initially, as mentionned in the OP : it interferes with the other code in ThisWorkbook, and not all the menus get hidden (in this case, the formula bar or the cell names still show). I'm not sure if it's related to the code itself or how it's being run...

As for the master file not being locked, Greg's solution has a password involved, so there's no easy possibility to edit the file anyway. However, it still provides me with a bit of code I could use in other files that don't run much code on file opening

EDIT : as it is, the Loader File has become a good idea for how my projects will evolve.

@22Anand

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #1 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread