Solved: Stop user accessing worksheet "Administartor"

[Pete]

see attached workbook
Hi

Need to stop any user if they try and click the worksheet "Administrator" (tab) a messagebox appears with warning "Cannot Access Administrator Worksheet, You Do Not Have Access Right"......

[Bob Phillips]

Why not just hide it?

[Pete]

no tried that keep on gettiung around that............so just thought code might work this time......with a warning thrown in...

[CreganTur]

Put the following code into a new module in your workbook. It will get the current PC UserName. You can then compare the UserName to either a single value (if only one person has 'Admin rights', or you can compare it to an array of people who have Admin rights).

You can use this value to give or deny access to your worksheet.

[vba]Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
Environ$ ("Username")

Dim lngLen As Long, lngX As Long
Dim strUserName As String

strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function[/vba]

Now, go to the code window for your Admin worksheet and use the Worksheet_Activate event to evaluate the username that is trying to access the worksheet. Example:
[vba]Sub Worksheet_Activate()
If fOSUserName <> 'Insert your UserName here as string Then
MsgBox "You are not authorized to view this worksheet."
End If
End Sub[/vba]

I'm not completely familiar with Excel, so I don't know the method to change the active worskheet back to Sheet1, but that's pretty much what you need to do.

HTH

[Oorang]

The problem is, that will break if the user disables macros. The best you can do if you want to keep it hidden is to go into the vb project and make the sheet VeryHidden (so it doesn't show up in the "unhide sheets" list. Change the codename and worksheet name to something less obvious, and the password protect the vbproject. That will at least stop users who don't know code.

Additionally I would share the workbook and turn on change tracking (because even if they clear the log, then you have an empty log to clue you in).

Out of curiousity what settings are on the admin tab that you trying to protect?

[mdmackillop]

You would also need to add code to force enabling of macros. There are some examples in the KB.

[Pete]

thanks for teh feedback......great works better then my own verison.......

[Oorang]

Another thing to consider is to password protect the workbook. This cause the data to be non-user readable if they decide to open the workbook in notepad, etc.