If obsolete - deactivate all but one macro

[remeng]

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!

 

[mikrom]

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.

 

[Andrzejek]

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

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[green]
    'Message Box optional[/green]
    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

 

[combo]

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

 

[mikrom]

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

 

[remeng]

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

 

[mintjulep]

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.