Solved

Alert User That Macro's Are Disabled

Posted on 2013-01-19
13
196 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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
 
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VBA – How to zip a PowerPoint presentation from excel? 3 35
need count numbers IN ranges 25 30
Excel VBA Script 9 52
Unique List in UserForm 3 21
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

739 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