Seeking knowledge on certain advanced options

I have a number of macro tools & calculators used by others. Most of the end users have little to no VBA knowledge.

Is there any code which would:

1. Check if the options listed below are enabled?
2. Automatically enable these without the end users consent?
3. If the last point is not possible then prompt the user to enable?

for the following Excel options:

• "Trust access to the VBA Project Object Model"
• Developer tab (i.e. Show on Excel ribbon)

(There were 1 or 2 other options I can't think of right now but these two were the main ones).

There is, to my knowledge, no way to show the developer tab via VBA.

And there's no way to enable access to the VBA project programmatically -- it would be a massive security risk.

Originally Posted by shg

There is, to my knowledge, no way to show the developer tab via VBA.

And there's no way to enable access to the VBA project programmatically -- it would be a massive security risk.

Security implications understood.

Well if I can't enable via code (question 2), do you know of a way of detecting the state of these options (question 1) so I can give the user a little MsgBox explaining what he/she needs to turn on?

I have a vague recollection of seeing this done before (albeit on a different feature - it was a specific VBA Tool Reference from memory). In the case I recall, the code detected if the feature wasn't enabled and displayed a form telling the user what box they needed to tick and then, to top it off, took the user right to the very option screen they needed to access! (It didn't auto-enable but I was impressed that it checked & held the end users hand right to the point where they had to manually enable!)

But do you think I can find this code again now? No!

vbaproject access is set via a registry key which may or may not exist-if it doesn't then the default is untrusted access. you can either try and read that key or simply try and access a component of the vbproject and check for errors

I'm not clear why you need the users to have the developer tab visible-why don't you use your own tab?

Originally Posted by RHCPgergo

You could hide all the worksheets but one and display a textbox or something that tells the user to reopen the file with macros enabled.

I appreciate your help. However this code isn't what I was looking for. (You are referring to the 'Force User to Enable Macros' code. I want to force options like VBA project).

Originally Posted by JosephP

vbaproject access is set via a registry key which may or may not exist-if it doesn't then the default is untrusted access. you can either try and read that key or simply try and access a component of the vbproject and check for errors

Both good ideas. I will look into reading registry keys.

With the Developer Tab - because I have difficulty explaining to non-VBA users how to install Add-Ins. This helps eliminate one of the steps. (I am looking at creating an easy add-in installer - but that's a topic for another thread some day).

you don't have to be writing code at runtime to require access to the vbp

regarding the developer tab that is not the only way to install add-ins and you could easily replicate any buttons from that tab on your own custom tab if required

tell Rob Bovey that ;-)

additionally macro viruses are not particularly common nowadays

that it's bad practice to ask users to lower their security. some of his add-ins have required that

ime, if you need access to the vbp, you need it period.

true but since we don't know what the op is doing I don't think you can say that it is necessarily bad practice or unnecessary

I reckon we're still on topic if meandering down some side trails ;-)

@ JosephP & Kyle123 - Interesting comments

Originally Posted by JosephP

regarding the developer tab that is not the only way to install add-ins

Agreed. One of my current projects is a 'no-brainer' XLAM installer for users with limited VBA knowledge.

Would it be possible (preferably not by registry checking if possibly avoid) to determine whether a certain VBA Tools Reference is enabled and enable if not? (Or at least prompt the user to enable if not?)

Originally Posted by Kyle123

Why do you want to do this? Requiring programmatic access is not good practice when distributing workbooks...Do you really need to write code at run time?

Please forgive my lack of VBA knowledge. I don't think I will need access to "Trust access to the VBA Project Object Model" after all.