夜雨聆风学习资料网

ARTICLE · 1395672

Excel-分级权限+密码登录+单元格修改留痕日志

Excel-分级权限+密码登录+单元格修改留痕日志
1. Excel不用上系统!分级权限+修改留痕,谁改了哪个单元格、改前改后全部记下来
2. 工厂台账安全方案:A组只能填入库、B组仅看出库,改动自动记录,可追溯
3. 带审计日志的权限台账:登录区分权限,单元格修改自动抓取原值、新值、操作人
之前做了分级权限,能控制谁能看谁能填,但有个短板——别人进去修改数据,你不知道什么时候改、改了哪个格子、原来内容是什么。
这个版本补齐:登录校验 + 分级权限控制 + Worksheet_Change自动捕获单元格修改,写入操作日志表。
✅ 适配工厂入库、出库台账;
✅ 记录:操作人、时间、工作表、单元格地址、修改前原值、修改后新值;
✅ 权限组:入库员、出库员、管理员;
✅ 重要提醒:仅微软Excel可用,WPS部分精简版事件宏不稳定,文件保存为 .xlsm
准备工作
新建3张工作表,名字严格一致:
1.  入库台账 :入库员权限可编辑;出库员只能查看;管理员全部权限
2.  出库台账 :出库员权限可编辑;入库员只能查看;管理员全部权限
3.  操作日志 :自动写入修改记录,表头A-F列
- A:操作人账号|B:操作时间|C:工作表名|D:单元格位置|E:修改前原值|F:修改后新值
说明:权限配置在代码里,你可以自行增删账号;登录弹窗用UserForm;修改留痕靠工作表变更事件。
第一步:用户窗体(登录窗体 UserForm1)
窗体控件:
- 2个Label:账号、密码
- 2个TextBox: txtUser 、 txtPwd (密码框把PasswordChar设为*)
- 2个CommandButton: cmdLogin (登录)、 cmdCancel (取消)
窗体代码(UserForm1)
vba    
Option Explicit
Public LoginUser As String
Public LoginRole As String '角色:admin / inUser / outUser
Private Sub cmdCancel_Click()
    LoginUser = ""
    LoginRole = ""
    Unload Me
End Sub
Private Sub cmdLogin_Click()
    Dim u As String, p As String
    u = Trim(Me.txtUser.Text)
    p = Trim(Me.txtPwd.Text)
    '账号配置,自行新增删除
    Select Case u
        Case "admin"
            If p = "123456" Then
                LoginUser = u
                LoginRole = "admin"
            Else
                MsgBox "密码错误!", vbExclamation
                Exit Sub
            End If
        Case "rukuyuan"
            If p = "111111" Then
                LoginUser = u
                LoginRole = "inUser"
            Else
                MsgBox "密码错误!", vbExclamation
                Exit Sub
            End If
        Case "chukuyuan"
            If p = "222222" Then
                LoginUser = u
                LoginRole = "outUser"
            Else
                MsgBox "密码错误!", vbExclamation
                Exit Sub
            End If
        Case Else
            MsgBox "账号不存在", vbCritical
            Exit Sub
    End Select
    Unload Me
End Sub
第二步:模块【Module1】全局变量、登录入口、权限设置
vba    
Option Explicit
Public g_User As String
Public g_Role As String
Sub StartLogin()
    Dim frm As New UserForm1
    frm.Show
    If frm.LoginUser = "" Then
        MsgBox "未登录,文件将锁定所有工作表", vbInformation
        Call LockAllSheet
        Exit Sub
    End If
    g_User = frm.LoginUser
    g_Role = frm.LoginRole
    Call SetPermission
    MsgBox "登录成功!当前账号:" & g_User & ",角色:" & g_Role, vbInformation
End Sub
'统一锁定全部工作表
Sub LockAllSheet()
    Dim sht As Worksheet
    For Each sht In ThisWorkbook.Worksheets
        On Error Resume Next
        sht.Protect Password:="pwd123", UserInterfaceOnly:=True
        On Error GoTo 0
    Next sht
End Sub
'按角色分配权限
Sub SetPermission()
    Call LockAllSheet '先全部上锁
    Select Case g_Role
        Case "admin"
            '管理员:全部可编辑,取消保护
            Sheets("入库台账").Unprotect Password:="pwd123"
            Sheets("出库台账").Unprotect Password:="pwd123"
        Case "inUser"
            '入库员:仅入库台账解锁
            Sheets("入库台账").Unprotect Password:="pwd123"
        Case "outUser"
            '出库员:仅出库台账解锁
            Sheets("出库台账").Unprotect Password:="pwd123"
    End Select
End Sub
'写入日志公共过程(给Change事件调用)
Sub WriteLog(shtName As String, cellAddr As String, oldVal As Variant, newVal As Variant)
    Dim logSht As Worksheet
    Dim lastR As Long
    Set logSht = ThisWorkbook.Sheets("操作日志")
    lastR = logSht.Cells(logSht.Rows.Count, "A").End(xlUp).Row + 1
    logSht.Cells(lastR, "A").Value = g_User
    logSht.Cells(lastR, "B").Value = Now()
    logSht.Cells(lastR, "C").Value = shtName
    logSht.Cells(lastR, "D").Value = cellAddr
    logSht.Cells(lastR, "E").Value = oldVal
    logSht.Cells(lastR, "F").Value = newVal
End Sub
第三步:ThisWorkbook 工作簿事件 + 工作表Change事件(重点!抓原值新值)
双击工程窗口  ThisWorkbook ,粘贴下面代码:
vba    
Option Explicit
Private Sub Workbook_Open()
    '打开文件自动弹出登录框
    Call StartLogin
End Sub
'注意:要捕获修改前的值,需要先存入数组,Change里拿
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim oldVal As Variant
    Dim newVal As Variant
    Dim c As Range
    '日志表本身修改不记录,避免循环触发
    If Sh.Name = "操作日志" Then Exit Sub
    '未登录不记录
    If g_User = "" Then Exit Sub
    Application.EnableEvents = False
    On Error GoTo ErrHandle
    For Each c In Target
        newVal = c.Value
        '关键:撤销获取原值
        Application.Undo
        oldVal = c.Value
        '再把值写回去
        c.Value = newVal
        Call WriteLog(Sh.Name, c.Address, oldVal, newVal)
    Next c
ErrHandle:
    Application.EnableEvents = True
    If Err.Number <> 0 Then
        Debug.Print "日志记录出错:" & Err.Description
    End If
End Sub
⚠️ 重点原理说明
 这里用  Undo  获取修改前原值,是VBA做单元格留痕最省事稳定的方案;
限制:批量一次性粘贴大片区域也能逐条记录;但有一个边界:撤销栈不能被别的操作打断。
注意点:
1. 开启事件开关,防止写日志的时候反复触发Change死循环
2. 操作日志工作表排除,不记录日志表改动
3. 工作表保护参数  UserInterfaceOnly:=True ,宏可以后台修改,普通界面用户受保护限制
避坑清单 
1. 每次打开文件自动登录;关闭文件重新打开需要重新登录;
2. 账号密码在窗体代码里修改,后期想升级可以单独放一张【账号表】读取;
3. 撤销方式取原值:如果修改后立刻执行了别的操作(宏、刷新),原值抓取会失效;追求高稳定性审计系统方案,需要用类模块+数组预存(适合后面单独出进阶版);
4. 插入行、删除行也会触发记录,会记录整行变动;
5. 保护密码  pwd123 ,代码里统一替换成你自己的密码;
6. 日志表不要给普通用户编辑权限,管理员可查看导出。
每日持续分享职场办公自动化小技巧,专业vba开发,关注收藏不迷路。
Excel-vba分级权限+操作日志|入库员/出库查看/管理员密码登录,自动记录谁什么时候登录
标题:Excel高级录入窗体查询+修改本条记录 升级版,录完自动追加台账
Excel-工厂数据分析零基础完整思路!一线管理通用分析法,看完直接会做报表、找问题、降损耗
Excel-vba台账怕崩、文件损坏?定时自动备份,后台静默存副本
VBA双条件/多条件匹配外部Excel,自动带回数据,找不到标记【无匹配】

回首页,搜索你想要的文档

在首页搜索框输入关键词,快速定位资料

文库首页搜索指引 立即搜索

相关学习资料