在MSExcel中使特定的单元格为只读

I need to convert a cell read-only if certain conditions apply.

如果应用某些条件，我需要将单元格转换为只读。

Example:

例子:

If in cell L there is a list of options (e.g. Android or Java) and if the user selects one of the options (e.g. android) then cell M should be read-only.

如果在cell L中有一个选项列表(如Android或Java)，如果用户选择其中一个选项(如Android)，那么cell M应该是只读的。

If user selects the other option (e.g. Java) then cell M should not be read only, since M also contains a list of options that are related to the selected user option (e.g. Java types).

如果用户选择了其他选项(例如Java)，那么单元格M不应该只被读取，因为M还包含与所选用户选项相关的选项列表(例如Java类型)。

1 个解决方案

#1

This is for cells L1 and M1

这是细胞L1和M1

Enter the following event macro in the worksheet code area:

在工作表代码区域输入以下事件宏:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim L As Range, M As Range
    Set L = Range("L1")
    Set M = Range("M1")
    If Intersect(Target, L) Is Nothing Then Exit Sub
    ActiveSheet.Unprotect
    M.Locked = False
    If L.Value = "Android" Then
        M.Locked = True
    End If
    ActiveSheet.Protect
End Sub

Because it is worksheet code, it is very easy to install and automatic to use:

由于是工作表代码，安装非常容易，使用也很自动:

1. right-click the tab name near the bottom of the Excel window
2. 右键单击Excel窗口底部附近的选项卡名称
3. select View Code - this brings up a VBE window
4. 选择视图代码——这将打开一个VBE窗口
5. paste the stuff in and close the VBE window
6. 将这些东西粘贴进来并关闭VBE窗口。

If you have any concerns, first try it on a trial worksheet.

如果您有任何问题，请先在试验工作表上试一试。

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

如果保存工作簿，宏将与它一起保存。如果您在2003年以后使用Excel版本，那么必须将文件保存为.xlsm而不是.xlsx

To remove the macro:

删除宏:

1. bring up the VBE windows as above
2. 打开VBE窗口，如上所示
3. clear the code out
4. 清晰的代码了
5. close the VBE window
6. 关闭VBE窗口

To learn more about macros in general, see:

要了解关于宏的更多信息，请参见:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

和

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

http://msdn.microsoft.com/en-us/library/ee814735(v = office.14). aspx

To learn more about Event Macros (worksheet code), see:

要了解更多关于事件宏(工作表代码)的信息，请参见:

http://www.mvps.org/dmcritchie/excel/event.htm

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

必须启用宏才能正常工作!

EDIT1:

EDIT1:

in the worksheet:

在工作表中:

and then in the VBA Window:

然后在VBA窗口:

EDIT#2:

编辑# 2:

Here is a version that will handle a block of cells:

这是一个可以处理单元格块的版本:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim L As Range, M As Range
    Set L = Range("L1:L10")
    Set M = Range("M1")
    If Intersect(Target, L) Is Nothing Then Exit Sub
    ActiveSheet.Unprotect
    For p = 1 To 10
        Range("M" & p).Locked = False
        If Range("L" & p).Value = "Android" Then
            Range("M" & p).Locked = True
        End If
    Next p
    ActiveSheet.Protect
End Sub