[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 202
  • Last Modified:

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
0
Geekamo
Asked:
Geekamo
  • 7
  • 6
1 Solution
 
Saqib Husain, SyedEngineerCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now