사용자 별 개별 워크 시트 보호 (Microsoft Excel)

Dim bOK2Use As Boolean

Private Sub btnOK_Click()

Dim bError As Boolean     Dim sSName As String     Dim p As DocumentProperty     Dim bSetIt As Boolean

bOK2Use = False     bError = True     If Len(txtUser.Text) > 0 And Len(txtPass.Text) > 0 Then         bError = False         Select Case txtUser.Text             Case "user1"

sSName = "u1sheet"

If txtPass.Text <> "u1pass" Then bError = True             Case "user2"

sSName = "u2sheet"

If txtPass.Text <> "u2pass" Then bError = True             Case Else                 bError = True         End Select     End If     If bError Then         MsgBox "Invalid User Name or Password"

Else         'Set document property         bSetIt = False         For Each p In ActiveWorkbook.CustomDocumentProperties             If p.Name = "auth" Then                 p.Value = sSName                 bSetIt = True                 Exit For             End If         Next p         If Not bSetIt Then             ActiveWorkbook.CustomDocumentProperties.Add _               Name:="auth", LinkToContent:=False, _               Type:=msoPropertyTypeString, Value:=sSName         End If

Sheets(sSName).Visible = True         Sheets(sSName).Unprotect (txtPass.Text)

Sheets(sSName).Activate

bOK2Use = True         Unload UserForm1     End If End Sub

Private Sub UserForm_Terminate()

If Not bOK2Use Then         ActiveWorkbook.Close (False)

End If End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim w As Worksheet     Dim bSaveIt As Boolean

bSaveIt = False     For Each w In Worksheets         If w.Visible Then             Select Case w.Name                 Case "u1sheet"

w.Protect ("u1pass")

w.Visible = False                     bSaveIt = True                 Case "u2sheet"

w.Protect ("u2pass")

w.Visible = False                     bSaveIt = True             End Select         End If     Next w     If bSaveIt Then         ActiveWorkbook.CustomDocumentProperties("auth").Delete         ActiveWorkbook.Save     End If End Sub

Private Sub Workbook_Open()

UserForm1.Show End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

If Sh.Name <> "Main" Then         If Sh.Name <> ActiveWorkbook.CustomDocumentProperties("auth").Value Then             Sh.Visible = False             MsgBox "You don't have authorization to view that sheet!"

End If     End If End Sub