PDA

View Full Version : Tricky worksheet Problem....



Pete
07-23-2008, 12:33 AM
See attached workbook

Hi

Problem not sure how to solve this one.....i have managed to get this far with help/assistance from this board.

Here is the problem....when the user click the worksheet "Administrator" a msg appears say "you are not authorised to access this worksheet" but the user can still view the worksheet....

How do i stop them physically seeing it........without hide....and still making the worksheet visible in the workbook...

Pete
07-23-2008, 12:34 AM
The problem is that........

i do not other people to view other peoples log in details.............

Simon Lloyd
07-23-2008, 01:14 AM
Use something like: (not tested)

Dim Sht as Worksheet
Dim uName as string
uName= environ("username")
for each sht in sheets
if sht.name=uName then
sht.visible=true
else
sht.visible=xlsheetveryhidden
end if
Next sht
thats the idea anyway, i haven't looked at your example, with the above code the only sheet visible to the user will be the one with his/her windows login name.

Bob Phillips
07-23-2008, 01:28 AM
I did raise this point yesterday. If they can't see it, why not hide it?

Pete
07-23-2008, 01:30 AM
If i hide it some idioit well just unhide it.......easy to get around.....

Bob Phillips
07-23-2008, 01:44 AM
Not if you use xlSheetVeryHidden in VBA. If they are that dumb, and that determined to meddle, Excel is not your platform.

Simon Lloyd
07-23-2008, 01:48 AM
xlSheetVeryHidden hides the sheet from the Hide/Unhide interface on the worksheet menubar, it can only be undone in VBA!

Thanks for the nudge Bob, i didn't write the code in excel just live here so didn't notice my Faux Pas (xlveryhidden should have been xlsheetveryhidden).

Aussiebear
07-23-2008, 01:52 AM
Pete, look at the possibility of setting permissions. Then set the visibility of certain sheets depending on the permissions as per their log on stauts.

Pete
07-23-2008, 03:56 AM
Ok guys nearly worked this out......using a old bit of VBA Coding.....but slight problem....

sequence

1. if you click the administrator worksheet it ask for a password = AAAA

2. once you type password......allows admin person to view worksheet...

3. But need a macro button to go back / reset back to verison where the users types in the password.....

4. without closing down the worksheet and re opening to reset....

see attached workbook and test it

Bob Phillips
07-23-2008, 05:16 AM
You do like to make things complicated don't you.

I got totally confused as to whichj sheet was which, so this code ie inevitabley not setting the rights ones, but it gives ths gist, you can do all the twiddly bits



Private mmSheet As String

Private Sub Workbook_Open()
mmSheet = ActiveSheet.Name
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim ScreenUpdating As Boolean
Dim PasswordEntered As String

If Sh.Name = "Administrator" Then

Application.EnableEvents = False
ScreenUpdating = Application.ScreenUpdating
Application.ScreenUpdating = False

PasswordEntered = InputBox("Password:")
If PasswordEntered = Sheet3.Password Then

Sheet2.Visible = xlSheetVisible
Sheet3.Visible = xlSheetVeryHidden
Worksheets(mmSheet).Activate
End If
Application.EnableEvents = True
Application.ScreenUpdating = ScreenUpdating
End If
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
mmSheet = Sh.Name
End Sub