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

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

2、eets If sht.Name Worksheets(1).Name Then sht.Visible = 2 End If Next ThisWorkbook.SaveAs ThisWorkbook.Path & & ThisWorkbook.Name, , Password:=123456, WriteresPassword:=excel2010 Me.Saved = True Application.DisplayAlerts = TrueEnd Sub（登录窗口绘制）（登录窗口属性设置）（Userform1代码窗口）（登录系统按钮属性设置）（退出系统按钮属性设置）其它控件属性设备略U

3、serform1（登录窗口）代码：Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)Application.QuitThisWorkbook.Close FalseEnd SubPrivate Sub CommandButton2_Click() 关闭系统 UserForm1.Hide Application.Quit ThisWorkbook.Close FalseEnd SubPrivate Sub UserForm_Activate() TextBox1.Text = If ComboBox1.

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

5、ue Next MsgBox 管理员登录成功 TextBox1.Text = Application.Visible = True UserForm1.Hide ElseIf ComboBox1.Text = 受限用户A And TextBox1.Text = 123 Then Worksheets(2).Visible = True Worksheets(3).Visible = 2 Worksheets(4).Visible = 2 MsgBox 登录成功 Application.Visible = True TextBox1.Text = UserForm1.Hide ElseIf Co

6、mboBox1.Text = 受限用户B And TextBox1.Text = 456 Then Worksheets(3).Visible = True Worksheets(4).Visible = True Worksheets(2).Visible = 2 MsgBox 登录成功 Application.Visible = True TextBox1.Text = UserForm1.Hide Else MsgBox 输入错误！ TextBox1 = End IfEnd SubPrivate Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.

7、ReturnInteger, ByVal Shift As Integer) 输入完毕回车 If KeyCode = 13 Then If ComboBox1.Text = 管理员 And TextBox1.Text = admin Then Dim i% For Each sht In Worksheets sht.Visible = True Next MsgBox 管理员登录成功 Application.Visible = True UserForm1.Hide ElseIf ComboBox1.Text = 受限用户A And TextBox1.Text = 123 Then Work

8、sheets(2).Visible = True Worksheets(3).Visible = 2 Worksheets(4).Visible = 2 MsgBox 登录成功 Application.Visible = True UserForm1.Hide ElseIf ComboBox1.Text = 受限用户B And TextBox1.Text = 456 Then Worksheets(3).Visible = True Worksheets(4).Visible = True Worksheets(2).Visible = 2 MsgBox 登录成功 Application.Vi

9、sible = True UserForm1.Hide Else MsgBox 输入错误！ TextBox1 = End If End 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) 用户所在行If cname = Sh

10、eets(设置).Range(a & cno).Value And Password = Sheets(设置).Range(B & cno).Value Then 密码对 Unload pass If cno = 2 Then 是管理员 For Each sht In Worksheets sht.Visible = True 显示全部表单 Next Else 是一般用户 sw = 0 s = Sheets(设置).Range(C & cno) For Each sht In Worksheets If sht.Name = s Then Sheets(s).Visible = True 显示

11、 sw = 1 Sheets(登录).Visible = False End If Next If sw = 0 Then MsgBox 没有你可以看的工作表，去找管理员更正 End IfElse 密码不对 j = j - 1 If j 0 Then MsgBox 密码不对。还可以输入 & j & 次。 TextBox1.Text = Else Unload pass MsgBox 密码不对。你已经试了3次了。再见！ End IfEnd IfApplication.EnableEvents = True 用所有事件End SubPrivate Sub TextBox1_Change()Pass

12、word = TextBox1.Text 获得密码End SubPrivate Sub UserForm_Initialize()r = Sheets(设置).Range(A65536).End(xlUp).Row 用户最后行For i = 2 To r 增加用户名 Me.ComboBox1.AddItem Sheets(设置).Cells(i, 1).ValueNext iMe.ComboBox1.SetFocusComboBox1.Value = Sheets(设置).Range(A2).Value 默认用户End SubPrivate Sub CommandButton3_Click() 退出 Unload passEnd Sub