Enter password to enable macros...

[Ignition1]

Hi

A workbook I have contains an "Update" VBA macro used by the administrator, this spreadsheet once "updated" is sent round to various people.

What I would to happen is that when the admin sends round the spreadsheet, the prompt that says "This workbook contains macros, enable/disable" etc does not appear. BUT when the admin clicks on the button linked to the Update macro - an input box appears asking for a password which enables macros and then runs the macro.

So I guess the macro would also have to disable macros at the end of it running so that it can be sent round.

Is this possible?

 

[Andrew Poulsom]

You can't enable/disable macros in a macro. If you could where would the security be?

 

[RichardMGreen]

You could always have a userform or input box that is shown when the update button is pressed. Only the Admin would know the password and if the password is incorrect or not entered then the macro doesn't run.

 

[Arcticwarrio]

You can put a password on the script itself

 

[Ruddles]

You can also test the login name of the user at the start of the update macro and exit immediately if it's not the administrator.

 

[Arcticwarrio]

Sub moveme()
    If InputBox("Please enter the Password", "Admin Password") = "password" Then
    
        'script   

    Else
        MsgBox ("Incorrect Password")
    End If
End Sub

 

[Ignition1]

Thanks guys - I'll think about a workaround.

Basically I was trying to avoid the security warning pop up when it's sent to other people except when the workbook is being opened by the macro creator. But then the check to do that is itself a macro so it doesn't really work.

The only alternative is running the macro from a different workbook so that the end result is in the spreadsheet I want to send but the macro is in a workbook that I keep.

 

[Jonmo1]

You might also consider storing your macros in another book that only you and your admin have access to.

Have those macros do their work on a different book that doesn't contain macros.

Then distribute that book to your users.