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

or

如何基于Excel中另一个单元格中的值锁定或解锁单元格?

在某些情况下,您可能需要根据另一个单元格中的值锁定或解锁单元格。 例如,如果单元格A1包含值“Accepting”,则需要解锁范围B4:B1; 如果单元格A1包含值“拒绝”,则锁定。 你怎么能做到这一点? 这篇文章可以帮助你。

使用VBA代码基于另一个单元格中的值锁定或解锁单元格

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

箭头蓝色右泡 使用VBA代码基于另一个单元格中的值锁定或解锁单元格


下面的VBA代码可以帮助您基于Excel中另一个单元格中的值锁定或解锁单元格。

1。 右键单击工作表选项卡(工作表中需要根据另一个单元格中的值锁定或解锁的单元格),然后单击 查看代码 从右键菜单。

2。 然后将以下VBA代码复制并粘贴到代码窗口中。

VBA代码:基于另一个单元格中的值锁定或解锁单元格

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("A1") = "Accepting" Then
        Range("B1:B4").Locked = False
    ElseIf Range("A1") = "Refusing" Then
        Range("B1:B4").Locked = True
    End If
End Sub

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

从现在起,当您在单元格A1中输入值“Accepting”时,范围B1:B4被解锁。

当在单元格A1中输入“拒绝”值时,指定的范围B1:B4被自动锁定。


箭头蓝色右泡相关文章:


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.
    Christian Conti Gennaro · 3 months ago
    Hi, could you kindly check the reason why it doesn't work?

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("A3:A37").Value <> "" Then
    Range("B3:B37").Locked = True
    ElseIf Range("A3:A37") = "" Then
    Range("B3:B37").Locked = False
    End If

    If Range("B3:B37").Value <> "" Then
    Range("A3:A37").Locked = True
    ElseIf Range("B3:B37") = "" Then
    Range("A3:A37").Locked = False
    End If

    End Sub


    Thank you very much in advance!!!
  • To post as a guest, your comment is unpublished.
    Zk · 3 months ago
    Is it possible to lock a cell, when it reaches a certain value?
  • To post as a guest, your comment is unpublished.
    Mitchyll · 10 months ago
    How would the code be if I wanted to lock a block of cells (Rows 6, 7, and 8/Letters D through U as well as cells F5 and J5) and have them unlock when I put an "X" in cell E5? Thanks in advance!
    • To post as a guest, your comment is unpublished.
      crystal · 8 months ago
      Hi MitchyII,
      Do you mean the specified block of cells have been locked manually in advance and just want to unlock them by typing an "X" in cell E5?
      If remove "X" from cell E5, you do want to lock the ranges again?
      I need more details of the problem.
      Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Joe · 1 years ago
    Would you kindly advice me on how to correct this? Thank you in advance.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("A:A") = "SLOW MOVING" Then
    Range("B:B").Locked = True
    ElseIf Range("A:A") = "OVER STOCK" Then
    Range("B:B").Locked = True
    ElseIf Range("A:A") = "NORMAL" Then
    Range("B:B").Locked = False
    End If
    End Sub
    • To post as a guest, your comment is unpublished.
      Noexpert · 1 years ago
      Not being a VB expert i would say you have too many "Elseif" - if you change them all to just IF except for the last one then hopefully that will work.
      Basically If X do this, If Y do this, if Z do this, if none of those - do this.
  • To post as a guest, your comment is unpublished.
    Joe · 1 years ago
    Can you guide me on what's wrong here please? Thank you in advance.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("K:K") = "OVER STOCK" Then
    Range("S:S").Locked = True
    ElseIf Range("K:K") = "SLOW MOVING" Then
    Range("S:S").Locked = True
    ElseIf Range("K:K") = "NORMAL" Then
    Range("S:S").Locked = False
    ElseIf Range("K:K") = "SHORTAGE" Then
    Range("S:S").Locked = False
    End If
    End Sub
  • To post as a guest, your comment is unpublished.
    KB · 1 years ago
    I prepare a warehouse stock management in excel template.To deliver a stock i have to issue a gate pass.I want to each gate pass,corresponding data will be updated in daily stock take page.With the change of gate pass serial no,the row will be locked and next will be filled up.
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good Day,
      Would be nice if you can upload your workbook here. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Andor Veres · 1 years ago
    Hi,
    I am really new to this.
    I have been trying to put an invoicing system together in excel.
    I created 3 sheets.
    1. Invoice template (Invoice)- Just a generic invoice that is sent to my agents weekly.

    2. A data sheet (Data sheet) to be exact - where the invoice can read the company name adress etc, so if anything changes the invoice will be automatically updated.

    3. A calendar tab (Calendar 2018) to be exact - that is referenced in the invoice template, and puts the corresponding date and invoice number on the actual invoices.

    What I want to do.
    The calendar tab would be my main page, I added a status drop down cell for each week with options "Active" and "Closed". I would like to Lock the whole "Invoice" tab if the corresponding cell is set to "Closed".

    I hope you guys understand what I am trying to do.
    Thanks in advance.
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Dear Ando Veres.
      The below VBA code can help you. Please place the code into the sheet code window of Calendar 2018 change A1 to your drop down cell. Thank you.

      Private Sub Worksheet_Change(ByVal Target As Range)
      Dim xRg As Range
      On Error Resume Next
      Set xRg = Intersect(Target, Range("A1"))
      If xRg Is Nothing Then Exit Sub
      If Target.Validation.Type >= 0 Then
      If Target.Value = "Closed" Then
      Sheets("Data Sheet").Protect
      ElseIf xRg.Value = "Active" Then
      Sheets("Data Sheet").Unprotect
      End If
      End If
      End Sub
  • To post as a guest, your comment is unpublished.
    Mira · 1 years ago
    Hi! Can someone help me? I have to lock/freeze a cell. That cell is linked to another one and has a value which is changing every minute. What I what to do is to keep the value for a certain minute/ hour. How can I do that without copying it and paste it as a value?
  • To post as a guest, your comment is unpublished.
    Horace · 1 years ago
    can some one correct this pls>>>

    Private Sub Worksheet_Change(ByVal Target As Range)
    For i = 7 To 100
    If Range("Cells(D, i)") = "Loan" Then
    Range("Cells(V, i):Cells(X, i)").Locked = True
    ElseIf Range("Cells(D, i)") = "Savings" Then
    Range("Cells(Q, i):Cells(U, i)").Locked = True
    Range("Cells(W, i):Cells(X, i)").Locked = True
    ElseIf Range("Cells(D, i)") = "ShareCap" Then
    Range("Cells(Q, i):Cells(U, i)").Locked = True
    Range("Cells(V, i)").Locked = True
    End If
    Next i
    End Sub
  • To post as a guest, your comment is unpublished.
    margie · 1 years ago
    Hi! need some advise.
    Is there a way not to allow a cell to be updated unless it has satisfied a condition on another cell?
    Sample: if the cell A is not updated it will not allow me to change the value of cell B to complete.

    Appreciate the feedback.
    Thanks!
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Dear Margie,
      Please try below VBA code.

      Dim PreVal As String
      Dim NextVal As String
      Private Sub Worksheet_Activate()
      PreVal = Range("A1")
      NextVal = Range("A1")
      End Sub
      Private Sub Worksheet_Change(ByVal Target As Range)
      If (Target.Count = 1) And (Target.Address = "$A$1") Then
      NextVal = Range("A1")
      End If
      End Sub
      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      If Target.Count = 1 Then
      If Target.Address = "$A$1" Then
      PreVal = Range("A1")
      ElseIf (Target.Address = "$B$1") Then
      If PreVal = NextVal Then
      Application.EnableEvents = False
      Range("A1").Select
      Application.EnableEvents = True
      End If
      End If
      End If
      End Sub
  • To post as a guest, your comment is unpublished.
    SHAINA · 1 years ago
    WHAT WILL BE THE CODE IF I WANT TO LOCK CELL E1, E2, E3 .............. FOR SPECIFIC TEXT (LETS SAY "P") ON CELL B1, B2, B3.................RESPECTIVELY.

    THANKS IN ADVANCE
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good Day
      Please try below VBA script.

      Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Count = 1 Then
      If Target.Address = Range("A1").Address And Target.Value = "A" Then
      Range("B1").Locked = True
      ElseIf Target.Address = Range("A2").Address And Target.Value = "A" Then
      Range("B2").Locked = True
      ElseIf Target.Address = Range("A3").Address And Target.Value = "A" Then
      Range("B3").Locked = True
      End If
      End If
      End Sub
  • To post as a guest, your comment is unpublished.
    Kristoffer · 1 years ago
    Hello,

    I have tried your code and edit a little bit, but i can't work out what i do wrong here?

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("A40") <> "" Then
    Range("D40:E40").Locked = False
    ElseIf Range("A40") = "" Then
    Range("D40:E40").Locked = True
    End If
    End Sub


    My though about it was if there is nothing in it (A40). Then i want to locked by VBA. If A40 contain something, then i want it to be unlocked. I hope you can see the sense of it.


    Regards Kristoffer
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good Day,
      There is nothing wrong with your code. It works well for me.
      • To post as a guest, your comment is unpublished.
        KeeranB · 1 years ago
        Hi. I too cannot get this code to work. It does absolutely nothing. As if the code isn't even there?? I'm very new to VBA and have a basic understanding on it. Is this code being run as-is, or does it have to have stuff added to it as well for it to run? Or turned into a Macro (which I don't really get why because that's a recording of instructions, on my understanding of them)
  • To post as a guest, your comment is unpublished.
    leo · 1 years ago
    please can someone help me with the following.
    I want to insert pictures of student in one sheet, appears in another sheet based on their names
    To create a navigating plane to assist users
    To assign a particular sheet(s) to a user
    To create an interface for the workbook
    To create a login page
  • To post as a guest, your comment is unpublished.
    Ant · 2 years ago
    Hi, I'm trying to achieve this, but I get an error that VBA is unable to set the Locked property of the Range class if the sheet has been protected. Unprotecting the sheet will then negate the cell being locked.

    How to get around this?

    Thanks for any help.
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Dear Ant,
      The below VBA code can help you solve this probem. Thank you for your comment.

      Private Sub Worksheet_Activate()
      If Not ActiveSheet.ProtectContents Then
      Range("A1").Locked = False
      Range("B1:B4").Locked = False
      End If
      End Sub
      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      Dim xRg As Range, xRgA As Range
      On Error Resume Next
      Application.EnableEvents = False
      Set xRg = Range("B1:B4")
      Set xRgA = Range("A1")
      If Intersect(Target, xRg).Address <> Target.Address _
      Or xRgA = "Accepting" Then
      Application.EnableEvents = True
      Exit Sub
      ElseIf ActiveSheet.ProtectContents _
      And Intersect(Target, xRg) = Target _
      And xRgA.Value = "Refusing" Then
      xRgA.Select
      End If
      Application.EnableEvents = True
      End Sub
      • To post as a guest, your comment is unpublished.
        Christian · 1 years ago
        Hi,

        Is it possible to have this VBA lock one set of cells/unlock another based on this? For example Range B1:B4 is unlocked and C1:C4 is locked for "accepting" and then B1:B4 is locked and C1:C4 is unlocked for "refusing"?


        Thanks,
        Christian
        • To post as a guest, your comment is unpublished.
          crystal · 1 years ago
          Dear Christian,
          Is your worksheet protected?
    • To post as a guest, your comment is unpublished.
      Henry · 1 years ago
      You'll want to use the interface line in the workbook so when you open the file, it protects the sheets but allow macros to make changes anyway;

      Private Sub Workbook_Open() 'This goes into "ThisWorkbook"

      Worksheets("Order Tool").Protect Password:="Pwd", UserInterFaceOnly:=True

      End Sub
    • To post as a guest, your comment is unpublished.
      Memo · 2 years ago
      Did you resolved? I have the same problem
      • To post as a guest, your comment is unpublished.
        crystal · 1 years ago
        Dear Memo,
        Please try the below VBA code.

        Private Sub Worksheet_Activate()
        If Not ActiveSheet.ProtectContents Then
        Range("A1").Locked = False
        Range("B1:B4").Locked = False
        End If
        End Sub
        Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Dim xRg As Range, xRgA As Range
        On Error Resume Next
        Application.EnableEvents = False
        Set xRg = Range("B1:B4")
        Set xRgA = Range("A1")
        If Intersect(Target, xRg).Address <> Target.Address _
        Or xRgA = "Accepting" Then
        Application.EnableEvents = True
        Exit Sub
        ElseIf ActiveSheet.ProtectContents _
        And Intersect(Target, xRg) = Target _
        And xRgA.Value = "Refusing" Then
        xRgA.Select
        End If
        Application.EnableEvents = True
        End Sub
  • To post as a guest, your comment is unpublished.
    Sheetal Rao · 2 years ago
    How can lock/unlock a variable cell e.g. when cell is [=INDEX(A16:L35,MATCH(W5,A16:A35,0),MATCH("PAY",A16:L16,0))]
    • To post as a guest, your comment is unpublished.
      Mohammed Mandlaywala · 1 years ago
      I want a simple VBA command which I am unable to figure out please help
      If cell A1 is Balnk then Cell A2 is locked and If Cell A1 contains any Value then Cell A2 is unlocked
      Similarly if cell A2 is blank then Cell A3 is locked and if Cell A2 contains any value then Cell A3 is unlocked
      and so on as many cells as per requirement in any part of the sheet.