根据其他单元格输入锁定Excel 2013单元格 [英] Lock Excel 2013 cells based on other cell input

问题描述

我有一个工作表，其中有许多不同的列供输入.有问题的列是D，H和I.D和H是下拉列表.我是任何文字输入.我试图将VBA脚本放在一起，以便在D2中进行选择时将锁定H2和I2.如果在H2中进行了选择，则将锁定D2和I2.如果在I2中输入了文本，则将锁定D2和H2.最后，需要对D，H和I的整个列进行此操作，以使这些列中的每个单元格都具有相同的属性，即，如果I16填充，D16和H16将锁定，依此类推.

I have a worksheet with many different columns for input. The columns in question are D, H, and I. D and H are drop downs. I is any text input. I am trying to put together a VBA script so that if a selection is made in D2 then that will lock H2 and I2. If a selection is made in H2 then that will lock D2 and I2. If text is entered in I2 then that will lock D2 and H2. Lastly, this will need to be done for the entire column of D, H, and I so that each cell in those columns have the same property i.e. D16 and H16 will lock if I16 is filled and so on and so forth.

如果这也可以通过公式实现，那么我不介意.

If this can be implemented by formulas as well I do not mind.

  `  Private Sub Worksheet_Change(ByVal Target As Cells)
            If ActiveSheet.Cells(2, 4).Text = True Then 
'This is what I don't understand. I don't know what to set the text to. I'm trying to say if there's anything in the cell Then do the following...
                ActiveSheet.Cells(2, 8).Locked = True
                ActiveSheet.Cells(2, 9).Locked = True
            Else
                ActiveSheet.Cells(2, 8).Text = True Then
                ActiveSheet.Cells(2, 4).Locked = True
                ActiveSheet.Cells(2, 9).Locked = True
            Else
                ActiveSheet.Cells(2, 9).Text = True Then
                ActiveSheet.Cells(2, 4).Locked = True
                ActiveSheet.Cells(2, 8).Locked = True
            End If
    End Sub`

推荐答案

尝试使用此代码，但请记住，除非保护工作表，否则将单元格锁定为无影响.还要注意，以范围为目标的ByVal目标是 Worksheet_Change 参数的正确语法(不是以单元格的ByVal目标).

Try this code, but bear in mind locking cells as no affect unless the worksheet is protected. Also note that ByVal Target as Range is the correct syntax for the Worksheet_Change argument (not ByVal Target as Cells).

Private Sub Worksheet_Change(ByVal Target As Range)

If Len(Target) Then

    Select Case Target.Column

        Case Is = 4

            Me.Unprotect "pwd"
            Cells(Target.Row, 8).Locked = True
            Cells(Target.Row, 9).Locked = True
            Me.Protect "pwd"

        Case Is = 8

            Me.Unprotect "pwd"
            Cells(Target.Row, 4).Locked = True
            Cells(Target.Row, 9).Locked = True
            Me.Protect "pwd"

        Case Is = 9

            Me.Unprotect "pwd"
            Cells(Target.Row, 4).Locked = True
            Cells(Target.Row, 8).Locked = True
            Me.Protect "pwd"

    End Select

End If

End Sub

这篇关于根据其他单元格输入锁定Excel 2013单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！