Disable All Macros in Workbook if user not authorized

[Negi1984]

Hi All,

I have a workbook where around 35 macros created to complete and reconcile multiple tasks.

Now I want macro can only be run by 3 users like user A & user B & user C. Apart from this nobody can able to run any macros in the workbook.

Can anybody suggest me, how to solve this issue.

Regards,
Rajender

 

[most]

Add password (and/or even a login) to all macros, with InputBox.
Maybe even setup a Forms.

 

[Negi1984]

Hi Most,

Thanks for the suggestion. If not any alternative to add one more macro instead of updating all existing macros?

 

[most]

I'm not sure I completely understand your question. No, there are no built in security feature in Excel which will solve your requirement.

 

[JumboCactuar]

The only suggestion I have is to make all your modules private so they don't show in the window.

Then create a new macro with password input box > which if validated could pop up a userform or prompt to run others macros.

 

[Michael M]

Add a password to the sheet and an Inputbox for the required users "password"
AND simply password protect the VBE !!
While it can be broken, you average user generally won't be able to !!
Deleting macros IS possible but I wouldn't advise it !!

 

[Negi1984]

Hi,

Thank you , I am think now to protect module and hide from preview and in workbook open event write a code which will hide all objects from the workbook, if user is not authorized.

Could you please suggest , where I need to do modification in below code and hide all objects in workbook while open workbook.

Function test()
test = False
'Pass the user to the checking function
If allowed(Environ("username")) Then
    test = True
    Else: MsgBox ("You can't use macros here")
End If
End Function


--------------------------------------------------
Public Function allowed(ByRef TF As String) As Boolean
    Dim arr As Variant
    arr = Array("name1", "name2", "name3")
    For Each Item In arr
        If Item = TF Then
        allowed = True
        Exit Function
        Else: allowed = False
        End If
    Next

End Function