Hi all

I am trying to produce a shared workbook for the weekly payroll, which needs to be accessed by various units, but for them to only have access to the tabs which relate to their units. Due to tight deadlines, they are all likely to be working on the file at the same time - hence the need for a shared workbook.

I have produced a file which contains a macro to restrict the view of the file by user using very hidden worksheets (see macro 1 below) and various other macros to protect/ unprotect sheets and the workbook, restrict selection or allow selection on worksheets, etc.


Macro 1:

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal IpBuffer As String, nSize As Long) As Long
Public UserName As String
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call Unprotect_Workbook_and_Sheets
Call Hide_Sheet(UserName)

End Sub

Private Sub Workbook_Open()
UserName = fNTUserName

Call UnHide_Sheet(fNTUserName)
Call Protect_Workbook_and_Sheets

End Sub

Sub UnHide_Sheet(UserName As String)

Select Case UserName

Case "xxx", "xxx", "xxx" ' Admin View
Worksheets("MAN_DR_Timesheet").Visible = True
Worksheets("MAN_DR_Submission").Visible = True
Worksheets("MAN_WK_Timesheet").Visible = True
Worksheets("MAN_WK_Submission").Visible = True
Worksheets("MID_DR_Timesheet").Visible = True
Worksheets("MID_DR_Submission").Visible = True
Worksheets("LEE_Timesheet").Visible = True
Worksheets("LEE_Submission").Visible = True
Worksheets("PUR_Timesheet").Visible = True
Worksheets("PUR_Submission").Visible = True
Worksheets("Weekly Input").Visible = True
Worksheets("Weekly Rates").Visible = True
Worksheets("Control").Visible = True
Worksheets("Lookup").Visible = True
Worksheets("Notice").Visible = False


Case "xxx", "xxx" ' MAN DR View
Worksheets("MAN_DR_Timesheet").Visible = True
Worksheets("MAN_DR_Submission").Visible = True
Worksheets("Notice").Visible = False


Case "xxx", "xxx" ' MAN WK View"
Worksheets("MAN_WK_Timesheet").Visible = True
Worksheets("MAN_WK_Submission").Visible = True
Worksheets("Notice").Visible = False


Case "xxx", "xxx", "xxx" ' MID DR View
Worksheets("MID_DR_Timesheet").Visible = True
Worksheets("MID_DR_Submission").Visible = True
Worksheets("Notice").Visible = False


Case "xxx", "xxx" ' LEE View
Worksheets("LEE_Timesheet").Visible = True
Worksheets("LEE_Submission").Visible = True
Worksheets("Notice").Visible = False


Case "xxx", "xxx" ' PUR View
Worksheets("PUR_Timesheet").Visible = True
Worksheets("PUR_Submission").Visible = True
Worksheets("Notice").Visible = False


End Select

End Sub

Sub Hide_Sheet(UserName As String)

Worksheets("Notice").Visible = True
Worksheets("MAN_DR_Timesheet").Visible = xlVeryHidden
Worksheets("MAN_DR_Submission").Visible = xlVeryHidden
Worksheets("MAN_WK_Timesheet").Visible = xlVeryHidden
Worksheets("MAN_WK_Submission").Visible = xlVeryHidden
Worksheets("MID_DR_Timesheet").Visible = xlVeryHidden
Worksheets("MID_DR_Submission").Visible = xlVeryHidden
Worksheets("LEE_Timesheet").Visible = xlVeryHidden
Worksheets("LEE_Submission").Visible = xlVeryHidden
Worksheets("PUR_Timesheet").Visible = xlVeryHidden
Worksheets("PUR_Submission").Visible = xlVeryHidden
Worksheets("Weekly Input").Visible = xlVeryHidden
Worksheets("Weekly Rates").Visible = xlVeryHidden
Worksheets("Control").Visible = xlVeryHidden
Worksheets("Lookup").Visible = xlVeryHidden

End Sub
Function fNTUserName() As String

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
fNTUserName = Left$(strUserName, lngLen - 1)
Else
fNTUserName = ""
End If

End Function



Everything is working fine until I save the file as a shared workbook.

Has anyone got any ideas on this can be done?

Many thanks

Rick