×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

If obsolete - deactivate all but one macro

If obsolete - deactivate all but one macro

If obsolete - deactivate all but one macro

(OP)
Hi Guys,

I'm not sure how to do this the simplest way and was hoping for some ideas. I have a excel document that contains multiple macros. One of them adds a banner across the menu worksheet saying that the file is obsolete.

To reactivate the sheet, an admin needs to enter a password. This then removes the banner.

The problem is that a user can still use the macros on the other worksheets by clicking the trigger buttons. What I'd like to do is deactivate all of the macros except the one that can re-activate all of the macros.

The first though was to use a CALL macro at the beginning of each macro to check the status of the document. Is there a simpler way to do it though?

A second thought maybe restricting the macros that can be run when the on open macro runs. I'm not sure if that is possible though.

Thanks for the help!

RE: If obsolete - deactivate all but one macro

IMO - as you already mentioned - you have to customize each macro so that it on the beginning checks the status and then decides whether to run further or not.

RE: If obsolete - deactivate all but one macro

Or check the status once "when the on open macro runs" and use this information in every macro, something like:

CODE

Option Explicit
...
Public blnRunMacros As Boolean
...
Sub OnOpenMacro()
'Check if the macros are OK to run, if so
blnRunMacros = True
...
End Sub

Sub AnyOtherMacro()

If Not blnRunMacros Then
    'Message Box optional
    MsgBox "The file is not 'Active' and Macros cannot run."
    Exit Sub
End If
...
End Sub 

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: If obsolete - deactivate all but one macro

Quote (remeng)

The problem is that a user can still use the macros on the other worksheets by clicking the trigger buttons.
You can disable or hide buttons and enable/unhide if the password is correct. In this scenario you have too:
- disable saving workbook (handle Workbook_BeforeSave event),
- limit macros visibility - not available from 'macros' box.

combo

RE: If obsolete - deactivate all but one macro

But in any case the macros have to be adjusted. This is not possible without adjusting all macros.

RE: If obsolete - deactivate all but one macro

(OP)
Thanks all. That's what I figured. I implemented the CALL option, and it works just fine. Most time simple is best.

RE: If obsolete - deactivate all but one macro

It seems simpler to me for the same macro that declares things "obsolete" hides or disables the "trigger buttons" (whatever they are).

Alternately, when obsolescence is declared, set all the sheets to .xlveryhidden = True; removing the ability/temptation to use obsolete data.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close