Hi,

I have a excel file with 16 spreadsheets and I would like to limit access of
certain users to certain spreadsheets in the file. Is there any solution to
this?

Any help is much appreciated.

Thanks & Regards

Hi,
this is possible. One way is to use the Workbook_Open &
Workbook_BeforeClose events. Use the Before_Close event to hide all the
sheets, and the Workbook_Open Event to show the sheets based on
username.... Something like this should start you off...this will show
sheets 1 to 5 (regardless of whether they are chart sheets or
worksheets) to John Doe..

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim intCnt As Integer
For intCnt = 1 To Me.Sheets.Count
Me.Sheets(intCnt).Visible = xlSheetVeryHidden
Next intCnt
End Sub


Private Sub Workbook_Open()
Dim strUser As String, intCnt As Integer

Let strUser = Environ("UserName")

Select Case strUser
Case "Doe, John"
Me.Sheets(Array(1,2,3,4,5)).Visible = xlSheetVisible
Case "Smith, John"
'''....etc...
Case Else

End Select
End Sub


Hth,
OJ

Perhaps I should mention that you need at least one sheet to be
visible.....this means my Workbook_BeforeClose code will error out....

For intCnt = 1 To Me.Sheets.Count should read
For intCnt = 2 To Me.Sheets.Count
where sheet 1 is a splash screen or something similar.

Hide the ones you don't want them to see. In VBA, you can very hide them

Worksheets("Sensitive Data").Visible = xlVeryHidden

This stops them unhdining via the format menu.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"bayhe" wrote in message
...
Hi,

I have a excel file with 16 spreadsheets and I would like to limit access
of
certain users to certain spreadsheets in the file. Is there any solution
to
this?

Any help is much appreciated.

Thanks & Regards

you could use visible = xlveryhidden, you would have to write a sub,
that asked the user name, then set the worksheets to hidden or not.
shouldn'y be that hard.


Good Luck
ross
www.methodsinexcel.co.uk