本文介绍了使用条件格式锁定/解锁单元格的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

如何使用条件格式基于另一个单元格(A2)中的值(是/否)锁定/解锁单元格(A1)?

How can I Lock/Unlock a cell(A1) based on value(yes/no) in another cell(A2) using conditional formatting?

推荐答案

在线上有很多资源展示了如何执行此操作.这是一篇有助于向我解释的文章: http://database.ittoolbox.com/groups/technical-functional/excel-l/how-to-lockunlock-an-excel-cell-based-on-the-contents- -of-other-cell-4625040

There's lots of resources online showing how to do this. Here's an article that helped explain it to me:http://database.ittoolbox.com/groups/technical-functional/excel-l/how-to-lockunlock-an-excel-cell-based-on-the-contents-of-another-cell-4625040

万一链接死了,这是它的要旨:

In case that link dies, here is the gist of it:

要执行所描述的操作,您将需要创建一个事件过程,只要工作表的内容发生更改,Excel就会调用该过程.首先打开Visual Basic窗口(按Alt + F11).您应该在左上方的窗格中看到树视图;在该视图中找到工作表的名称,然后双击该名称.这将在右侧的大窗格中打开与该工作表关联的代码模块.

To do what you are describing, you will need to create an event procedure that Excel will call whenever the contents of the worksheet are changed. Start by opening the Visual Basic window (press Alt+F11). You should see s tree view in a pane at the top left; find the name of the worksheet in that view, and double-click the name. This will open the code module associated with that worksheet in the large pane on the right.

在大窗格的顶部,您将看到两个下拉列表.最初,左边的将显示(常规),而右边的将显示(声明).单击左侧列表右端的三角形箭头,然后选择工作表".

At the top of the large pane you will see two drop-down lists. Initially, the one on the left will display (General), while the one on the right will display (Declarations). Click the triangular arrowhead at the right end of the left list, and select Worksheet instead.

Excel将自动添加SelectionChange事件过程的框架".那不是我们想要的,但这不会造成任何伤害.

Excel will automatically add the "skeleton" of a SelectionChange event procedure. That's not what we want, but it won't hurt anything.

Excel还将右侧下拉列表中的选择更改为SelectionChange.打开该列表,然后选择更改". Excel将添加第二个事件过程,该过程应如下所示:

Excel will also change the selection in the right-hand drop-down list to SelectionChange. Open that list and select Change instead. Excel will add a second event procedure, which should look like this:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

闪烁的光标将位于此骨架的空白行上.

The blinking cursor will be positioned on the blank line of this skeleton.

您要向Worksheet_Change过程中添加代码以检查G2单元格的内容,并更改G3:G66范围的状态.这需要一个IF语句和几个赋值语句:

You want to add code to the Worksheet_Change procedure to examine the contents of the G2 cell, and change the state of the G3:G66 range. This requires one IF statement, and a couple of assignment statements:

Private Sub Worksheet_Change(ByVal Target As Range)
    If ActiveSheet.Cells(2, 7).Text = "X" Then
        ActiveSheet.Range(Cells(3, 7), Cells(66, 7)).Locked = True
    Else
        ActiveSheet.Range(Cells(3, 7), Cells(66, 7)).Locked = False
    End If
End Sub

IF语句测试单元格G2的当前内容(Cells()方法采用行-2-和列号-7-标识单元格;列G为第七列).这两个赋值语句更改范围的Locked属性.一个锁定单元格,另一个锁定单元格.

The IF statement tests the current contents of cell G2 (the Cells() method takes a row -- 2 -- and a column number -- 7 -- to identify the cell; column G is the seventh column). The two assignment statements change the Locked property of the range; one locks the cells, the other unlocks them.

当然,如果您的代码更复杂,那么您将希望避免每次工作表上的任何内容更改时都运行它.为了避免执行代码过于频繁,可以使用传递给事件过程的Target参数:

Of course, if your code were more complicated, you would want to avoid running it every time anything on the worksheet changes. To avoid executing the code too often, you can use the Target parameter that is passed to the event procedure:

If Intersect(ActiveSheet.Cells(2, 7), Target) _
    Is Not Nothing Then

此条件语句使用Intersect()函数来确定单元格G2(ActiveSheet.Cells(2,7))是否包含在Target中.

This conditional statement uses the Intersect() function to determine if cell G2 ( ActiveSheet.Cells(2, 7)) is included in Target.

因此,完整的事件过程如下:

Therefore, the complete event procedure would look like:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(ActiveSheet.Cells(2, 7), Target) Is Not Nothing Then
        If ActiveSheet.Cells(2, 7).Text = "X" Then
            ActiveSheet.Range(Cells(3, 7), Cells(66, 7)).Locked = True
        Else
            ActiveSheet.Range(Cells(3, 7), Cells(66, 7)).Locked = False
        End If
    End If
End Sub

这篇关于使用条件格式锁定/解锁单元格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

05-28 22:33
查看更多