不同用户依权限查看不同工作表的VBA实现

1、.不同用户依权限查看不同工作表的VBA 实现（登录窗口1）（登录窗口2）.专业资料 .（登录窗口3）（模块 1 代码窗口 ）.专业资料 .This workbook代码窗口代码如下 ：Private Sub Workbook_Open()Application.Visible = FalseUserForm1.Show 1End SubPrivate Sub Workbook_BeforeClose(Cancel As Boolean)On Error Resume NextApplication.DisplayAlerts = FalseFor Each sht In WorksheetsI

2、f sht.Name Worksheets(1).Name Thensht.Visible = 2End IfNext.专业资料 .ThisWorkbook.SaveAs ThisWorkbook.Path & & ThisWorkbook.Name, ,Password:=123456, WriteresPassword:=excel2010Me.Saved = TrueApplication.DisplayAlerts = TrueEnd Sub（登录窗口绘制）.专业资料 .（登录窗口属性设置）.专业资料 .（ Userform1代码窗口 ）.专业资料 .（登录系统按钮属性设置）.专业资料

3、 .（退出系统按钮属性设置）其它控件属性设备略Userform1 （登录窗口 ）代码 ：Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)Application.QuitThisWorkbook.Close FalseEnd Sub.专业资料 .Private Sub CommandButton2_Click() 关闭系统UserForm1.HideApplication.QuitThisWorkbook.Close FalseEnd SubPrivate Sub UserForm_Activate(

4、)TextBox1.Text = If ComboBox1.ListCount 0 Then Exit Sub复合框中的条目数大于0 时不继续执行 。 如无此句，会导致出现重复用户名下拉列表。t = Array(管理员 , 受限用户A, 受限用户B)For i = 0 T o 2Controls(ComboBox1).AddItem t(i)Next iEnd SubPrivate Sub CommandButton1_Click()登录按钮If ComboBox1.Text = 管理员 And TextBox1.Text = admin ThenDim i%For Each sht In W

5、orksheets.专业资料 .sht.Visible = TrueNextMsgBox 管理员登录成功TextBox1.Text = Application.Visible = TrueUserForm1.HideElseIf ComboBox1.Text = 受限用户A And TextBox1.Text = 123 ThenWorksheets(2).Visible = TrueWorksheets(3).Visible = 2Worksheets(4).Visible = 2MsgBox 登录成功 Application.Visible = TrueTextBox1.Text = Us

6、erForm1.HideElseIf ComboBox1.Text = 受限用户B And TextBox1.Text = 456 ThenWorksheets(3).Visible = TrueWorksheets(4).Visible = TrueWorksheets(2).Visible = 2MsgBox 登录成功 Application.Visible = True.专业资料 .TextBox1.Text = UserForm1.HideElseMsgBox 输入错误 ！ TextBox1 = End IfEnd SubPrivate Sub TextBox1_KeyDown(ByV

7、al KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) 输入完毕回车If KeyCode = 13 ThenIf ComboBox1.Text = 管理员 And TextBox1.Text = admin ThenDim i%For Each sht In Worksheetssht.Visible = TrueNextMsgBox 管理员登录成功Application.Visible = TrueUserForm1.Hide.专业资料 .ElseIf ComboBox1.Text = 受限用户A And TextBox1.T

8、ext = 123 ThenWorksheets(2).Visible = TrueWorksheets(3).Visible = 2Worksheets(4).Visible = 2MsgBox 登录成功 Application.Visible = TrueUserForm1.HideElseIf ComboBox1.Text = 受限用户B And TextBox1.Text = 456 ThenWorksheets(3).Visible = TrueWorksheets(4).Visible = TrueWorksheets(2).Visible = 2MsgBox 登录成功 Appli

9、cation.Visible = TrueUserForm1.HideElseMsgBox 输入错误 ！ TextBox1 = End IfEnd IfEnd Sub.专业资料 .相关参考代码（含注释 ）：Private Sub CommandButton1_Click() 确定Dim sht As WorksheetApplication.EnableEvents = False 禁用所有事件cname = ComboBox1.Value 获得用户名cno = WorksheetFunction.Match(cname, Sheets(设置 ).Range(a1:a & r), 0)用户所在

10、行If cname = Sheets(设置 ).Range(a & cno).Value And Password = Sheets(设置 ).Range(B &cno).Value Then 密码对Unload passIf cno = 2 Then 是管理员For Each sht In Worksheets.专业资料 .sht.Visible = True 显示全部表单NextElse 是一般用户sw = 0s = Sheets( 设置 ).Range(C & cno)For Each sht In WorksheetsIf sht.Name = s ThenSheets(s).Visi

11、ble = True 显示sw = 1Sheets( 登录 ).Visible = FalseEnd IfNextIf sw = 0 Then MsgBox 没有你可以看的工作表，去找管理员更正End IfElse 密码不对j = j - 1If j 0 ThenMsgBox 密码不对 。还可以输入 & j & 次。 TextBox1.Text = ElseUnload passMsgBox 密码不对 。你已经试了3 次了 。 再见！ .专业资料 .End IfEnd IfApplication.EnableEvents = True 用所有事件End SubPrivate Sub TextBox1_Change()Password = TextBox1.Text 获得密码End SubPrivate Sub UserForm_Initialize()r = Sheets(设置 ).Range(A65536).End(xlUp).Row 用户最后行For i = 2 To r增加用户名Me.ComboBox1.A