Solved

Alert User That Macro's Are Disabled

Posted on 2013-01-19
13
191 Views
Last Modified: 2013-01-20
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
0
Comment
Question by:Geekamo
  • 7
  • 6
13 Comments
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 38797712
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
 
LVL 1

Author Comment

by:Geekamo
ID: 38797714
@ 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
 
LVL 1

Author Comment

by:Geekamo
ID: 38797717
@ 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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38797732
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
 
LVL 1

Author Comment

by:Geekamo
ID: 38797797
@ 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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38797809
You can find it on my profile.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 1

Author Comment

by:Geekamo
ID: 38797821
@ ssaqibh,

I sent you an email.

~ Geekamo
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38797846
It is working just fine. Make sure that the VBA window is closed.
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38797851
@ 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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38797860
I open the file afresh. It says ...DISABLED...

I enable macros. It says ENABLED


Try closing everything and then restarting.
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38797869
@ 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
 
LVL 1

Author Comment

by:Geekamo
ID: 38797896
@ 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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38798755
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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now