Access to Command Buttons

[Joe Carrigan]

Hi everyone.
Sorry to bug you all at the weekend, I have a spreadsheet that I need everyone in the office to have access to. Unfortunately I also need to restrict access to the command buttons which are on one sheet. I know this is possible as I saw it done before, can somebody please help me to do this?

My list of valid user (using their windows username) is on sheet “Admin”, and the buttons are in sheet “Instructions”
Thanks in advance for your assistance.
PS Excel 2003

 

[portews]

This will hide the buttons. Those that know what they are doing can still get to the code and run it, unless you've taken other steps such as passwording the code and setting the subs to private. (can they add their own name to the Admin list?) It's like putting a lock on the door, it will only keep the honest ones out.

Put this code in the Workbook_Open section. It will see if their Windows user ID is in the A column of the Admin page. If so, it will make the buttons visible. Of course, change your button names and ranges as necessary. You should start with the buttons invisible (put the code after "NotThere:" in the BeforeClose event to make sure they are by default).

Private Sub Workbook_Open()
On Error GoTo NotThere                                          'if the name is not found, it will error.  This will send it to error handling
If WorksheetFunction.Match(Environ("Username"), Sheets("Admin").Range("A1:A" & Sheets("Admin").Range("A" & Rows.Count).End(xlUp).Row), 0) Then
    With Sheets("Instructions")
        .OLEObjects("CommandButton1").Visible = True  'Shows the button.  Use this style if the buttons are Active X buttons
        .OLEObjects("CommandButton2").Visible = True
        .Buttons("Button 3").Visible = True                   'Shows the button.  Use this style if the buttons are Form Control buttons
    End With
End If
Exit Sub
NotThere:                                                            'Error handling
    With Sheets("Instructions")
        .OLEObjects("CommandButton1").Visible = False  'Hides the button.  Use this style if the buttons are Active X buttons
        .OLEObjects("CommandButton2").Visible = False
        .Buttons("Button 3").Visible = False                   'Hides the button.  Use this style if the buttons are Form Control buttons
    End With
On Error GoTo -1                                                   'resets the error handler
End Sub

I didn't try it on 2003, but I think it should work, nothing fancy here.

 

[Joe Carrigan]

Thank you.

This works a treat.