惊了!每天按时下班的男同事,私下竟用Excel搞「多人运动」!【excel教程】

全套Excel视频教程,扫码观看

编按:
哈喽,大家好!在日常工作中,工作表经常会被多人编辑,为此,我们往往需要为不同的人设置不同的查看权限,以实现工作表的分级保密。下面分享一种用VBA设置权限的方法,它拥有高逼格的用户登录界面,以及完善的分级保密机制,赶紧来看看吧!
提到“多人运动”,你一定以为小花一言不合就开车。你错了!作为一个正经的Excel教程作者,小花说的多人运动,指的是同一个Excel表格经常需要由多人编辑,供多人使用。
温馨提示:加入下面QQ群:1043683754,下载教程配套的课件练习操作。
Excel中的多人运动,往往需要赋予不同的人不同的查看权限,从而做到分级保密。简单地通过文件保护不能区分权限,而通过简单地隐藏工作表又太容易被取消,形同虚设!为此,小花深夜不睡,YY了一个VBA授权查阅套路,相信小花瓣看完一定会由衷叹一句:666!
第一步:建立权限表
赋予不同角色不同的查看权限是“多人运动”表格的重头戏,我们通过建立权限表来实现这一功能。从权限表第三行开始,我们需要将工作簿中的所有工作表名称依次填写;而从第二列开始的每一列,则用于填写每个角色的用户名、密码和对应的权限(用是否表示)。由此,我们可以自由添加工作表和查看角色,不受初始设置的限制。详见下表。
PS:建立权限表后,我们需要将文件保存为启用宏的工作簿,文件类型为.xlsm。
第二步:创建窗体
通过窗体验证用户权限是“多人运动”表格的门面,它让这套表的逼格瞬间提升了好几个档次有木有。如此装逼必备老少皆宜的窗体,该如何创建呢?
1. 按Alt+F11,或者点击开发工具中的Visual Basic按钮,打开VBA窗口。
2. 点击【插入】–【用户窗体】,在弹出的界面中绘制两个标签,分别为用户名和密码,同时插入两个文本框和两个命令按钮(取消和确定)。具体插入方式和Excel工作表中插入形状无异,此处不再赘述。
第三步:按钮代码
VBA代码是这套“多人运动”表格的灵魂,表格之所以能够实现酷炫的分权功能,全在于这寥寥数语!所以,拿走不谢!
具体操作如下:
1. 双击窗体中的“确定”按钮,将下述代码粘贴到VBA代码窗口中即可。该段代码可以实现单击“确定”按钮,即启动校验用户密码并将对应的表格显示出来的功能。
Private Sub CommandButton1_Click()Dim j, i As Integermaxr = Application.CountA(Sheets("权限表").Range("A:A"))maxc = Application.CountA(Sheets("权限表").Range("1:1"))If TextBox1.Value = "" Then MsgBox "用户名不能为空", vbInformation, "注意": Exit SubIf TextBox2.Value = "" Then MsgBox "密码名不能为空", vbInformation, "注意": Exit SubFor i = 2 To maxc u = Worksheets("权限表").Cells(1, i) k = Worksheets("权限表").Cells(2, i) If TextBox1.Text = u And TextBox2.Text = k Then Unload Me Application.Visible = True Application.EnableCancelKey = xlInterrupt For j = maxr to 3 step -1 ThisWorkbook.Activate If Sheets("权限表").Cells(j, i) = "是" Then Sheets(j - 2).Visible = xlSheetVisible Else Sheets(j - 2).Visible = xlSheetVeryHidden End If Next j If Sheets("权限表").Visible = xlSheetVisible Then pw = "excelinexcel" Sheets("权限表").Unprotect Password = pw Cells.Select Selection.EntireColumn.Hidden = False End If Exit Sub End IfNext iMsgBox "用户名或密码错误!"End Sub
2.双击窗体中的“取消”按钮,同样粘贴下述VBA代码。该段代码功能很简单,即退出Excel。
Private Sub CommandButton2_Click()Application.DisplayAlerts = FalseUnload MeApplication.Visible = TrueApplication.QuitApplication.EnableEvents = FalseEnd Sub
3. 在两段代码的下方,粘贴以下VBA代码,用于防止用户通过关闭窗体入侵。
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode <> 1 Then Cancel = TrueEnd Sub
4. 双击对象窗口中的ThisWorkbook,将下方代码粘贴到代码窗口中去。这两段代码实现了打开工作簿即弹出窗体开始验证,并保护和隐藏工作表行列,避免用户通过禁用宏来越权查看。
工作簿打开事件代码:
Private Sub Workbook_BeforeClose(Cancel As Boolean)Application.DisplayAlerts = FalseSheets(1).Visible = xlSheetVisibleDim sht As WorksheetFor Each sht In WorksheetsIf sht.Name <> "权限表" Thensht.Visible = xlSheetVeryHiddenElsesht.SelectCells.EntireColumn.Hidden = Truepw = "excelinexcel"sht.Protect Password = pwsht.EnableSelection = xlNoSelectionEnd IfNextApplication.Visible = FalseThisWorkbook.Close savechanges:=TrueEnd Sub
工作簿关闭事件代码
Private Sub Workbook_Open() Application.DisplayAlerts = False Application.EnableCancelKey = xlDisabled Application.Visible = False UserForm1.ShowEnd Sub
5. 保护VBA工程,确保用户无法删除、查看或修改。
操作步骤:
点击【工具】-【VBAProject属性】,弹出【VBAProject-工程属性】对话框,选择【保护】选项卡,输入【查看工程属性的密码】,点击【确定】即可。
完成以上操作步骤,一套完美的多人运动表格就搞定了,车也就开完了。此时关闭Excel再次打开即可体验多人运动的快感,随文附上演示作品,快拿去把玩把玩吧!最后,以一段打油诗结束今天的分享,下次再见吧!
多人运动有是非,
一不小心就被黑。
学它几句VBA,
再也不怕把锅背。
扫二维码免费学Excel等视频

Excel教程相关推荐

Excel教程:敬童年,用Excel做一个万花尺!

VBA实战入门教程(四):判断位数、奇偶性、迟到早退等,都得用它!

叫你作图对比数据,你看你都做了个啥?【Excel教程】

让工作提速百倍的「Excel极速贯通班」

(0)

相关推荐