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.

What is used to manage permissions? Are the permissions set on the filesystem, or in some other location? THere are a couple of methods to use VBA to get the logged-in user's id - eg: http://stackoverflow.com/questions/677112/how-to-get-logged-in-users-name-in-access-vba — Tim Williams, Jan 22 '14 at 21:11
@TimWilliams I use active directory that updates a database which manages permissions. — Seesharp, Jan 23 '14 at 10:59

Makah · Accepted Answer · 2014-01-22T21:48:21.100

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

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

1 Answers1