禁止用户编辑单元格，实际的单元格数据除外

I have a spreadsheet that I'm using to keep track of when reports are sent to me. For those reports I don't receive, I have a black cell (a cell filled black). Locking cells doesn't really work, since they can't change the data in the cells. (Full disclosure, it's a conditional format doing the filling, if the cell has "Client" in the cell, it fills black).

我有一个电子表格，用来记录何时向我发送报告。对于那些我没有收到的报告，我有一个黑色的细胞(一个充满黑色的细胞)。锁定单元格实际上不起作用，因为它们不能更改单元格中的数据。(完全公开，它是一个条件格式来填充，如果单元格中有“Client”，它将填充为黑色)。

How can I prevent users from changing the "client" cells? Some of the cells aren't black, others are. I know there's a Worksheet_Change that I could use, but I can't quite get that to determine the cell color before the user changes it.

如何防止用户更改“客户机”单元格?有些细胞不是黑色的，有些是黑色的。我知道有一个Worksheet_Change可以使用，但是我不能在用户更改之前确定单元格的颜色。

I was thinking I'd need perhaps a Worksheet_SelectionChange event?

我想我可能需要一个Worksheet_SelectionChange事件?

The crux is I can't think of a way to check a cell's fill color before a change, and if it's different after the change, do a messagebox or whatever. (Actually, I'd be checking if the cell was "Client" before the user changed it, and if so, don't allow a change).

关键是，我想不出在更改之前检查单元格的填充颜色的方法，如果在更改之后是不同的，就做一个messagebox之类的。(实际上，在用户更改单元格之前，我将检查该单元格是否为“Client”，如果是，则不允许更改)。

Am I overthinking this and missing something obvious? I'd like to find a solution where I don't just program which cells are filled, and check if those cells were changed.

我是不是想得太多了，错过了一些明显的东西?我想找到一种解决方案，我不只是规划哪些单元格被填充，并检查这些单元格是否被修改。

Edit: I just thought to try and combining those two Change events, and this seems to work, but I am not sure if it's the best way to do this: (This is in the worksheet module)

编辑:我只是想尝试并结合这两个变化事件，这似乎可行，但我不确定这是否是最好的方法(这是在工作表模块中)

Option Explicit
Dim isClient As Boolean

Private Sub worksheet_SelectionChange(ByVal target As Range)
If target.Column = 5 Or target.Column = 6 Then
    If target.Value = "Client" Then
        isClient = True
    Else
        isClient = False
    End If
End If
End Sub


Private Sub Worksheet_Change(ByVal target As Range)
If target.Column = 5 Or target.Column = 6 And isClient = True Then
    Debug.Print "This has 'client'"
    target.Value = "Client"
    isClient = False
End If
End Sub

1 个解决方案

#1

To know the old color before changing we can use Application.Undo

要在更改之前了解旧颜色，可以使用Application.Undo

Private Sub Worksheet_Change(ByVal target As Range)
  Dim newValue As Variant
  If target.Column = 5 Or target.Column = 6 Then

   newValue = target.Value
   Application.EnableEvents = False
    Application.Undo
    Debug.Print target.Value 'old value
    Debug.Print target.DisplayFormat.Interior.Color 'old color
    'if it_is_OK then target.Value = newValue
   Application.EnableEvents = True

  End If
End Sub

Or simply you can lock the cell when its value changed to "Client" :

或者，当它的值改为“客户端”时，您可以锁定单元格:

Private Sub Worksheet_Change(ByVal target As Range)

  If target.Column = 5 Or target.Column = 6 And target.Value = "Client" Then

    target.Worksheet.Unprotect
     target.Locked = True
    target.Worksheet.Protect

  End If
End Sub

#1