Solved

Alert User That Macro's Are Disabled

Posted on 2013-01-19
13
192 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

947 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

22 Experts available now in Live!

Get 1:1 Help Now