提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

数据录入或Excel输入后如何锁定或保护单元格?

假设你有一个工作表,只有一定范围的空白单元格需要输入数据,输入完数据后,你需要自动锁定单元格,以防止再次发生变化。 你怎么能做到这一点? 这篇文章可以帮助你。

数据输入或输入VBA代码后锁定或保护单元

Office选项卡在Office中启用选项卡式编辑和浏览,使您的工作更轻松......
Kutools for Excel - 最佳办公生产力工具将解决您的大部分Excel问题
  • 重用任何东西: 将最常用或最复杂的公式,图表和其他任何内容添加到您的收藏夹中,并在将来快速重复使用它们。
  • 超过20文本功能: 从文本字符串中提取数字; 提取或删除部分文本; 将数字和货币转换为英语单词...
  • 合并工具:多个工作簿和表格合二为一; 合并多个单元格/行/列而不丢失数据; 合并重复行和总和...
  • 拆分工具:根据价值将数据拆分为多个表格; 一个工作簿到多个Excel,PDF或CSV文件; 一列到多列......
  • 粘贴跳过 隐藏/过滤行; 数和总和 按背景颜色; 创建邮件列表和 通过Cell的价值发送电子邮件...
  • 超级过滤器: 创建高级过滤方案并应用于任何工作表; 排序 按周,日,频率等; 筛选 通过大胆,公式,评论......
  • 超过300强大的功能; 适用于Office 2007-2019和365; 支持所有语言; 在公司轻松部署; 全功能60天免费试用。

数据输入或输入VBA代码后锁定或保护单元

例如,空白单元格的一定范围是A1:F8。 在Excel中输入数据后,请按如下操作锁定这些单元格。

1。 请先解锁此范围,选择单元格并右键单击,然后选择 单元格格式 在右键菜单中,在 单元格格式 对话框,取消选中 锁定 盒子下 保护 标签,最后点击 OK 按钮。 看截图:

2。 点击 评论 > 保护工作表。 并指定一个密码来保护这个工作表。

3。 右键单击工作表标签,选择 查看代码 从右键菜单。 然后将下面的VBA代码复制并粘贴到代码窗口中。 看截图:

VBA代码:在数据输入或输入后锁定或保护单元

Dim mRg As Range
Dim mStr As String

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Range("A1:F8"), Target) Is Nothing Then
    Set mRg = Target.Item(1)
    mStr = mRg.Value
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xRg As Range
    On Error Resume Next
    Set xRg = Intersect(Range("A1:F8"), Target)
    If xRg Is Nothing Then Exit Sub
    Target.Worksheet.Unprotect Password:="123"
    If xRg.Value <> mStr Then xRg.Locked = True
    Target.Worksheet.Protect Password:="123" 
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Range("A1:F8"), Target) Is Nothing Then
    Set mRg = Target.Item(1)
     mStr = mRg.Value
End If
End Sub

注意:在代码中,“A1:F8”是您需要输入数据的范围; 和“123”是这个受保护的工作表的密码。 请根据需要更改它们。

4。 按 其他 + Q 键同时关闭 Microsoft Visual Basic for Applications 窗口。

输入数据到范围A1:F8的单元格后,它们将被自动锁定。 如果您尝试更改此范围的任何单元格内容,您将得到一个提示对话框。 看截图:


Office Tab - 在Excel中选项卡式浏览,编辑和管理工作簿:

Office选项卡将Web浏览器(如Google Chrome,Internet Explorer新版本和Firefox)中显示的选项卡界面带到Microsoft Excel。 它将是一个节省时间的工具,在您的工作中无可比拟。 见下面的演示:

点击免费试用Office Tab!

Excel的Office选项卡


相关文章:


Kutools for Excel - 最佳办公生产力工具提高80%的生产力

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能60天免费试用。
kte tab 201905

Office选项卡为Office提供选项卡式界面,使您的工作更轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Jenny · 2 months ago
    After implementing this code, I can no longer sign my signature in Excel's signature tool. What code can I add so that this won't occur? I can set it so that one can edit objects but as soon as I type something into one of my protected cells the options are reset and I can't use the signature box. Any help would be appreciated
  • To post as a guest, your comment is unpublished.
    Jim · 3 months ago
    Hi, something like this in google sheet?
    • To post as a guest, your comment is unpublished.
      crystal · 2 months ago
      Hi Jim,
      Didn't text in google sheet. Thanks for your comment.
  • To post as a guest, your comment is unpublished.
    Ryan Bullock · 3 months ago
    Hello there, how can I use this so that the user inputs the data in a range and then must click a submit button/cell to lock the cells?
    • To post as a guest, your comment is unpublished.
      crystal · 2 months ago
      Good day,
      Can't help you solving this problem yet. Thanks for your comment.
  • To post as a guest, your comment is unpublished.
    AW · 3 months ago
    If the cell is currently empty (but has previously been filled) is there a way to make it editable rather than protected?
  • To post as a guest, your comment is unpublished.
    M · 9 months ago
    it doesn't seem to work if i have some cells merged. is there a solution for that?
    • To post as a guest, your comment is unpublished.
      crystal · 8 months ago
      Hi,
      If there are merged cells in the specified range, please try the following code.

      Dim mRg As Range
      Dim mStr As String

      Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
      If Not Intersect(Range("A1:F8"), Target) Is Nothing Then
      Set mRg = Target.Item(1)
      mStr = mRg.Value
      End If
      End Sub
      Private Sub Worksheet_Change(ByVal Target As Range)
      Dim xRg As Range
      On Error Resume Next
      Set xRg = Intersect(Range("A1:F8"), Target)
      If xRg Is Nothing Then Exit Sub
      Target.Worksheet.Unprotect Password:="123"
      If xRg.Value <> mStr Then xRg.MergeArea.Locked = True
      Target.Worksheet.Protect Password:="123"
      End Sub
      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      If Not Intersect(Range("A1:F8"), Target) Is Nothing Then
      Set mRg = Target.Item(1)
      mStr = mRg.Value
      End If
      End Sub
  • To post as a guest, your comment is unpublished.
    SURENDRA · 9 months ago
    SIR I SUCCESS TO PROTECT AND LOCK CELL BUT I WANT TO EDIT CELLS WHEN THEY LOCKED ONLY THROUGH CELLS CLICK AND ASK PASSWORD TO EDIT CELL . HOW TO DO THIS ..?
    • To post as a guest, your comment is unpublished.
      crystal · 8 months ago
      Hi SURENDAR,
      Sorry can't help with that. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Prandeep · 10 months ago
    Sir, when pasting code after double click in selected entry cell which cell enter the data the cell did not permission to entry value please fix the problem modify the code.
    • To post as a guest, your comment is unpublished.
      crystal · 10 months ago
      Hi,
      The code has been updated with the problem solving, please have a try. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Jess · 11 months ago
    Hello, I'm trying to code so that a user can double-click and it will capture their username and timestamp. I was the cells to lock immediately after this is completed. I have the following in VBA, but it keeps debugging back to the "Target = Environ("USERNAME") & " " & Now()". I am VERY new and VERY inexperienced w/ VBA, so please bear with me if it's something small I'm doing wrong.

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Target = Environ("USERNAME") & " " & Now()
    Cancel = True

    End Sub

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xRg As Range
    On Error Resume Next
    Set xRg = Intersect(Range("A1:D45"), Target)
    If xRg Is Nothing Then Exit Sub
    Target.Worksheet.Unprotect Password:="GENERAL"
    xRg.Locked = True
    Target.Worksheet.Protect Password:="GENERAL"
    End Sub
    • To post as a guest, your comment is unpublished.
      crystal · 10 months ago
      Hi Jess,
      The below VBA code can help you.
      Note: When protecting the worksheet, please uncheck the "Select locked cells" option in the Protect sheet dialog box. Thank you for your comment.

      Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
      Dim xRg As Range
      On Error Resume Next
      If ProtectContents Then
      Set xRg = Intersect(Range("A1:D4"), Target)
      If xRg Is Nothing Then Exit Sub
      Target.Worksheet.Unprotect Password:="123"
      Target = Environ("USERNAME") & " " & Now()
      Target.Locked = True
      Target.Worksheet.Protect Password:="123"
      Cancel = True
      End If
      End Sub
  • To post as a guest, your comment is unpublished.
    Nazmul · 11 months ago
    when I get out of the currently working file and opened it again I found that new cell not locked after data entry, only the previous lock cell found lock. any solution
    • To post as a guest, your comment is unpublished.
      crystal · 10 months ago
      Hi Nazmul,
      You need to save the workbook as an Excel Macro-Enabled Workbook before closing it.
  • To post as a guest, your comment is unpublished.
    shreedhar · 11 months ago
    Thanks man, This Saved my job : D
  • To post as a guest, your comment is unpublished.
    Kim · 1 years ago
    Hi - this post was very helpful and works perfectly. However, my filters stop working when the cells lock. Is there a way around this? Thanks!
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Kim,
      Sorry can't help with this. The filter feature is disabled in a protected worksheet by default.
  • To post as a guest, your comment is unpublished.
    max m. marcus · 1 years ago
    hello there need some help


    im actually doing a working roster for a large group of people, and this sheet is accessible by all so that they can key in their requests for days off/ annual leaves etc. however i want to only limit a number of people on leave for each day (maximum 5 on leave) and after 5 leave requests are keyed in for the day, no body else can fill anymore requests for that particular date.


    is there any code/function that will calculate the number of specific requests perday then when the quota is reached, then the other cells are blocked for the requests as to not exceed? thanks in advance
  • To post as a guest, your comment is unpublished.
    Peter Stires · 1 years ago
    Good Morning,

    Is there anyway to prevent a user from right clicking "view code" and seeing the admins password?
  • To post as a guest, your comment is unpublished.
    Smith · 1 years ago
    Hello,
    Is there a way to lock only cells rather than the whole sheet? For example, if a user enters "Yes" in A2, then A2 would lock immediately as to not allow any changes. I would still like if others could still edit any other cell too. Thanks!
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Smith,
      The below VBA code can help you solving the problem. Please have a try and thank you for your comment.

      Private Sub Worksheet_Change(ByVal Target As Range)
      On Error Resume Next
      Target.Worksheet.Unprotect Password:="123"
      Target.Locked = True
      Target.Worksheet.Protect Password:="123"
      End Sub
  • To post as a guest, your comment is unpublished.
    jackie · 1 years ago
    Hi
    editing the code is there a way i can get it to lock all the cells in the range after data has been entered into one cell within that range ? so they can only enter data into one cell within the range not multiple.


    thank you
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Dear Jackie,
      The below VBA code can help you solve the problem.

      Private Sub Worksheet_Change(ByVal Target As Range)
      Dim xRg As Range
      Dim xSRg As Range
      On Error Resume Next
      Set xSRg = Range("A1:F8")
      Set xRg = Intersect(xSRg, Target)
      If xRg Is Nothing Then Exit Sub
      Target.Worksheet.Unprotect Password:="123"
      xSRg.Locked = True
      Target.Worksheet.Protect Password:="123"
      End Sub
  • To post as a guest, your comment is unpublished.
    jackie · 1 years ago
    Hi


    i want it to lock the range after entry into one cell in the range please can you advise how i need to edit this to do this thank you
  • To post as a guest, your comment is unpublished.
    oh yeah · 1 years ago
    is it have to coding?
  • To post as a guest, your comment is unpublished.
    Palash Somani · 1 years ago
    cells are not even editable...what went wrong don't understand pls help
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good Day,
      At first, you need to set specified cells to Unlocked for editig, and then protect the worksheet. And finally apply the VBA script.
  • To post as a guest, your comment is unpublished.
    Akshay · 1 years ago
    Hi,
    Sir i want to lock after one time input data !
    Like i am following one customer and write status , then any more employee cant edit or modify that !
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good Day,
      Method in this post can also help you to solve this problem.
  • To post as a guest, your comment is unpublished.
    Fair · 2 years ago
    Thanx for the coding, but I've a problem to put 2 coding in 1 sheet, pls help.
  • To post as a guest, your comment is unpublished.
    Subhash Deb · 2 years ago
    i like to lock only the edited cells. once i enter any data to the empty cells, i require to lock the edited cell to be automitically locked but not the empty cells.
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Dear Subhash,
      After using the code, only the edited cells are locked in the specified range. And you can still enter data into the empty cells in the specified range as you need. After fill in the blank cell, it will be locked automatically.
      • To post as a guest, your comment is unpublished.
        pradip · 1 years ago
        No, it locks the whole range at once after a data input into a cell in the range
        • To post as a guest, your comment is unpublished.
          crystal · 1 years ago
          Dear pradip,
          Which Office version do you use?
  • To post as a guest, your comment is unpublished.
    Jezamiah · 2 years ago
    For anyone who might have stumbled here since this comment.
    If you are having to unlock the worksheet EVERYTIME you fill in a cell then you need to first,

    - Unlock the spreadsheet
    - Highlight the area of the worksheet the code applies over
    - Right-click and go to 'format cells'
    - Go to the far right tab called 'Protection' and UNCHECK the 'Locked' section (Even if It appears as a solid fill instead of a tick)

    After that you will be able to enter data in multiple cells without having to unlock everytime. Bear in mind if you need to revisit locked cells to delete or edit information you may need to repeat the steps above.

    Hope this helps.
  • To post as a guest, your comment is unpublished.
    Stacy · 2 years ago
    I tried the code and seemed to work somewhat. It does however allow me to delete the date entered into the cell but only stops me when I try to enter something else. Is there a way to keep data from being deleted?
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Dear Stacy,
      The same problem does not appear in my case. The code prevent users from entering as well as deleting data from the specified range. Would you provide your Office version for further testing?
  • To post as a guest, your comment is unpublished.
    Gourav Goya · 2 years ago
    Hello Sir ,

    I am facing one problem with the same code while using this code with other cobe in VBA.

    Please suggest me some solution

    Thanks and Regards
    Gourav
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Dear Gourav,
      Sorry can't solve this problem.
  • To post as a guest, your comment is unpublished.
    rsrneuro · 2 years ago
    Thanks for the code. Please let me know how to do auto lock the cells (similar to the ones listed by you) ONLY AFTER saving the file
  • To post as a guest, your comment is unpublished.
    spence · 2 years ago
    Try this code instead:


    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MyRange As Range

    Set MyRange = Intersect(Range("A1:D100"), Target)
    If Not MyRange Is Nothing Then
    Sheets("Sheet1").Unprotect password:="hello"
    MyRange.Locked = True
    Sheets("Sheet1").Protect password:="hello"
    End If
    End Sub


    And remember to change range (A1:D100), password (hello) and sheet (Sheet1) names/numbers if it does not match the above :)
  • To post as a guest, your comment is unpublished.
    Nitin Ingole · 2 years ago
    This is not working when it reopen excel file please help
  • To post as a guest, your comment is unpublished.
    Pravallika p · 3 years ago
    Hello,

    I have a spreadsheet with the range A3:AN219, i would like to protect this range as and when input complete.
    Please help me on this. I tried above code but it is not working for me