Как предотвратить удаление или изменение ссылочных значений в Excel?

Question

У меня есть лист Excel, который содержит уникальные идентификаторы, и другой лист, который содержит записи, которые ссылаются по значению (через проверку данных - список) на эти идентификаторы из первого листа.

Я включил проверку данных на 2-м листе, что успешно предотвращает ввод значений в столбце ID, если они не существуют на 1-м листе.

Однако есть недостаток: Excel по-прежнему позволяет вводить недопустимое значение в столбец идентификатора на 2-м листе, удаляя или изменяя значение идентификатора на 1-м листе после его ввода на 2-м листе.

Как заставить Excel запретить изменение или удаление значения идентификатора на 1-м листе, если соответствующее значение идентификатора существует в проверенном столбце на 2-м листе?

Answer 1

Вот альтернатива без VBA, использующая условное форматирование. Он полагается на то, что пользователь знает, что делать, но если вы сделаете достаточно очевидным, что есть ошибка, у вас все будет в порядке (в зависимости от ваших пользователей).

1) Добавить формулу для обнаружения ошибок. В Sheet1 в ячейку B2 введите: =IF(OR(MAX(COUNTIF(ProductListItemId,ProductListItemId))>1,MIN(COUNTIF(ProductListItemId,OrdersItemID))=0),"ERROR","ok") Это формула массива , поэтому вы должны нажать Ctrl-Shift-Enter, а не просто Enter. Формула будет окружена {}.

2) Условное форматирование: Если вы применяете условное форматирование ко всей странице или достаточно большому диапазону, этого должно быть достаточно, чтобы остановить пользователя на его дорожках. Вы можете добавить инструкции, чтобы «отменить» изменения и повторить попытку.

Я должен отметить, что формула в B2 также проверяет наличие дубликатов в вашем Списке продуктов (элементы на листе 1).

Результат:

Answer 2

Как указал @ForwardEd, для этого требуется некоторый VBA. Следующий код сработает, когда что-то изменится в вашем списке уникальных идентификаторов, и проверит, существует ли старое значение в другом списке (ваш 2-й лист). Если оно существует, изменение будет отменено.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim vOld As Variant
    Dim vNew As Variant

    If Not Intersect([ProductListItemID], Target) Is Nothing Then
        vNew = Target.Value
        Application.EnableEvents = False
        Application.Undo
        vOld = Target.Value
        If WorksheetFunction.CountIf([OrdersItemID], Target) > 0 Then
            MsgBox "Change disallowed"
        Else
            Target.Value = vNew
            'MsgBox "Change OK"
        End If
        Application.EnableEvents = True
    End If
End Sub

Это должно быть в коде для Sheet1. Я предполагаю два именованных диапазона:

ProductListItemID (List of items on Sheet1 to be protected)
OrdersItemID (List of ItemID's in 2nd sheet)

Для удобства я включил оба диапазона на одном листе:

Объяснение: Когда в Sheet1 вносится изменение, макрос проверяет, находится ли он в пределах диапазона ProductListItemID. Если это так, он получает измененное значение (vNew), затем отменяет действие и захватывает предыдущее значение (vOld). Затем он проверяет, существует ли значение vOld в диапазоне OrdersItemID. Если это так, ячейка остается со старым значением, в противном случае новое значение восстанавливается.

Answer 3

Простым решением является использование события Worksheet_SelectionChange . Я собираюсь быть немного дерзким и использовать образ, который подготовил Роб Гейл . Я воспроизвел это изображение в этом ответе на случай, если что-нибудь случится с ответом Роба.

Код, приведенный ниже, должен блокировать ячейки, если соответствующий идентификатор элемента найден на листе 2. И затем, в следующий раз, когда ячейка выбрана, она должна разблокировать ячейки для будущих изменений.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim tRangeToProtect as Range
    Set tRangeToProtect = Intersect([ProductListItemID], Target)

    If Not tRangeToProtect Is Nothing Then
        Dim tCell as Range
        Dim tItemFound as Boolean
        tItemFound = False

        For Each tCell in tRangeToProtect  ' "Target" can be multiple cells. Must always hand this.
           tItemFound = tItemFound OR WorksheetFunction.CountIf([OrdersItemID],tCell) > 0
        Next tCell
        if tItemFound Then 
            tRangetoProtect.Locked = True
            Me.Protect UserInterfaceOnly = True
            ' Use this in conjunction with worksheet.Protect UserInterfaceOnly := True
    Else
        [ProductList].Locked = False ' Open this up for future checks and editing - remove any existing locks
    End If
End Sub

Это только грубое решение - может быть уточнено в зависимости от контекста вашего бизнеса. Кроме того, я не проверял это, поэтому ваш пробег может варьироваться.