|
发表于 2016-5-27 11:07
|
显示全部楼层
本楼为最佳答案
ThisWorkbook中代码- Private Sub Workbook_Open()
- Application.ScreenUpdating = False
- ActiveWorkbook.Unprotect Password:="123456"
- Dim sh As Worksheet
- For Each sh In Worksheets
- If sh.Name <> "登录" Then
- sh.Visible = xlSheetHidden
- Else
- sh.Visible = xlSheetVisible
- End If
- Next
- ActiveWorkbook.Protect Password:="123456", Structure:=True, Windows:=False
- Application.ScreenUpdating = True
- End Sub
复制代码 模块代码:- Sub 返回()
- Application.ScreenUpdating = False
- ActiveWorkbook.Unprotect Password:="123456"
- With Sheets("登录")
- .Visible = True
- ActiveSheet.Visible = False
- .Select
- End With
- Application.ScreenUpdating = True
- ActiveWorkbook.Protect Password:="123456", Structure:=True, Windows:=False
- End Sub
- Sub 登录()
- Dim arr, i%, n&, YH$
- arr = Sheet2.Range("a4:b12") '用户及密码
- With Sheet1
- If .[c5] = "" Then
- MsgBox "用户名不能为空!"
- Exit Sub
- End If
- For i = 1 To 9
- If .[c5] = arr(i, 1) Then Exit For
- Next
- If arr(i, 2) = .[C7] Then
- YH = Sheet1.[c5]
- Call SheetQH(Sheets("内容"))
- With Sheet3
- n = .Cells(Rows.Count, 1).End(xlUp).Row
- .Unprotect Password:="123456"
- .Range("A3:AE" & n).AutoFilter
- Select Case YH
- Case "华北"
- .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingColumns:=True, AllowFiltering:=True, Password:="123456"
- Case "北京"
- .Range("A3:AE" & n).AutoFilter Field:=4, Criteria1:=YH
- .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingColumns:=True, Password:="123456"
- Case "北京战略"
- .Range("A3:AE" & n).AutoFilter Field:=4, Criteria1:=YH
- .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingColumns:=True, Password:="123456"
- Case "天津"
- .Range("A3:AE" & n).AutoFilter Field:=4, Criteria1:=YH
- .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingColumns:=True, Password:="123456"
- Case "青岛、济南"
- .Range("A3:AE" & n).AutoFilter Field:=4, Criteria1:="=青岛", Operator:=xlOr, Criteria2:="=济南"
- .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingColumns:=True, Password:="123456"
- Case "华北IT招聘"
- .Range("A3:AE" & n).AutoFilter Field:=6, Criteria1:="=IT招聘", Operator:=xlOr, Criteria2:="=IT外包"
- .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingColumns:=True, Password:="123456"
- Case "华北培训"
- .Range("A3:AE" & n).AutoFilter Field:=6, Criteria1:="=培训"
- .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingColumns:=True, Password:="123456"
- Case "华北外包"
- .Range("A3:AE" & n).AutoFilter Field:=6, Criteria1:="<>培训", Operator:=xlAnd, Criteria2:="<>*招聘"
- .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingColumns:=True, Password:="123456"
- End Select
- End With
- Else
- MsgBox "密码有误!"
- End If
- End With
- End Sub
- Sub SheetQH(sht)
- Dim sh As Worksheet
- Application.ScreenUpdating = False
- ActiveWorkbook.Unprotect Password:="123456"
- If sht.Name <> ActiveSheet.Name Then
- With sht
- .Visible = True
- ActiveSheet.Visible = False
- .Select
- End With
- For Each sh In Worksheets
- If sh.Name <> sht.Name Then
- sh.Visible = xlSheetHidden
- End If
- Next
- End If
- Application.ScreenUpdating = True
- ActiveWorkbook.Protect Password:="123456", Structure:=True, Windows:=False
- End Sub
复制代码 自己在加工下! |
|