Alert User That Macro's Are Disabled

Hello Experts,

The workbook I created is *highly* dependent on the macro's of the workbook being enabled.  Now I realize I can't -force- a user to enable them - but at the very least I want to warn them that the macro's are disabled and basically the workbook won't function properly.

I would love if this was possible...

In cell A1.

If macro's are enabled, I want cell A1 to say "enabled".  If they are disabled, I want cell A1 to say "disabled".

Is this possible with only the use of a formula?

Thank you in advance for your help!

~ Geekamo
LVL 1
GeekamoAsked:
Who is Participating?
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
You can enter this formula in the cell

="Macros "&IF(ISERROR(enabled()),"disabled","enabled")

and this code in a USER module

Function enabled()
End Function
0
 
GeekamoAuthor Commented:
@ ssaqibh,

Thank you for the response!  I'm curious though, your solution involves code - which technically won't be run if the workbook is disabled.  (Or will it?)  What am I not getting here? I thought the solution would be dependent 100% of a formula.

~ Geekamo
0
 
GeekamoAuthor Commented:
@ ssaqibh,

Also, what do you mean by "User" Module?

I know I can create a module that always says "Module #" - then there's a "This WorkBook" module, and then like Page Names.  Is a user module one of the modules I already know about?

~ Geekamo
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Saqib Husain, SyedEngineerCommented:
Yes, the user module is Module #

The given code is a UDF which will work if macros are enabled. If macros are disabled it will throw an error and the ISERROR function will trap the error and give the "Disabled" message
0
 
GeekamoAuthor Commented:
@ ssaqibh,

At first it was working correctly, now I noticed it's not working correctly.  I'm confused.  lol I think it has something to do with the worksheet being changed on open.  Would you mind looking at my workbook to troubleshoot why it's not reporting the correct answer 100% of the time?  I suspect it has to do with other maybe the On Open event? Idk.  This is all beyond me.  I would prefer to email you this file, as I don't want to post it publicly.  Please let me know your thoughts, thanks.

~ Geekamo
0
 
Saqib Husain, SyedEngineerCommented:
You can find it on my profile.
0
 
GeekamoAuthor Commented:
@ ssaqibh,

I sent you an email.

~ Geekamo
0
 
Saqib Husain, SyedEngineerCommented:
It is working just fine. Make sure that the VBA window is closed.
0
 
GeekamoAuthor Commented:
@ ssaqibh,

It is closed.  

Just to be clear, when I open the workbook and disable macros - both of those formulas are returning enabled.

~ Geekamo
0
 
Saqib Husain, SyedEngineerCommented:
I open the file afresh. It says ...DISABLED...

I enable macros. It says ENABLED


Try closing everything and then restarting.
0
 
GeekamoAuthor Commented:
@ ssaqibh,

Hmm, weird.

I am opening the file I sent you from my sent folder - just to be sure I am testing it how you're testing it.  And when it opens, I disable macros - and both formulas are resulting in enabled.

Clearly, something is going on.  Are there any other solutions to do this?

~ Geekamo
0
 
GeekamoAuthor Commented:
@ ssaqibh,

Actually, in order to avoid any more time on this one - I am just going to use this method.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=379

It very well could be the better approach anyways - because my workbook is so heavily dependent on code.

Thank you very much for taking the time to help me with this!

~ Geekamo
0
 
Saqib Husain, SyedEngineerCommented:
It really beats me why this is happening. In my opinion there can be at least one way I can think of which would fail the vbaexpress method and that would be a time when a program disable events and during this time the file is saved.
0
All Courses

From novice to tech pro — start learning today.