How can I create an Excel spreadsheet which provides links to workbooks through buttons which are only enabled to the right user?

Question

How can I create an Excel spreadsheet which provides links to workbooks using buttons which are only enabled to the right user?

I want to create one index page/spreadsheet/workbook for multiple users to use in order to access numerous workbooks. I would like to grey out (disable) or remove buttons to workbooks that a user is not permitted to access.

I am not sure how to go about this, I read up on IRM. Although it does not show how I can grey out (disable) buttons that certain users cannot access.

I will very much appreciate everyone's input.

Thank you all in advance.

Answer 1

First of all you will need to protect your workbook with a password and create a Database for your users/permissions, set the DataBase Sheet as VeryHidden xlSheetVeryHidden.

DB Example:

+----------+----------+----------+----------+
|  Names   |           Allow                |
+          +----------+----------+----------+
|          | Button 1 | Button 2 | Button 3 |
+----------+----------+----------+----------+
| MrJones  | 1        | 0        | 0        |
| Makah    | 0        | 1        | 0        |
| Seesharp | 1        | 1        | 1        |
+----------+----------+----------+----------+

Now you can make a checkPermission sub that checks if the can execute the macro.

Function CheckPermission(byVal colIndex as Integer) As Boolean
    Dim userName As String, allow As Integer

    userName = Environ("USERNAME")
    CheckPermission= WorksheetFunction.VLookup(userName, Sheets("DB").[A:D], colIndex, False)
End Function

Finally you can use this code inside all macros.

Sub Button1()
  If Not CheckPermission(2) Then
    Exit Sub
  End If

  'Your code here!

End Sub