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

321
Alex R

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

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

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

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

0
Вы можете защитить первый лист, таким образом, запретив * любые * изменения в нем cybernetic.nomad 5 лет назад 0
Это заходит слишком далеко. Мне нужно сделать большое количество обновлений для обоих листов, и я хочу быть защищенным от случайного удаления значения, которое требуется на другом листе. Alex R 5 лет назад 0
вам, вероятно, понадобится небольшой кусочек кода VBA для мониторинга списков. Прежде чем изменить ячейку в заданном диапазоне, он может проверить другой диапазон, чтобы увидеть, есть ли он там. в зависимости от результатов вы можете позволить значению изменить или предотвратить выполнение запланированного действия. К сожалению, я очень слабый программист VBA. Если вы воспользуетесь Google, вы можете найти пример кода. Вы также можете обратиться к stackoverflow за помощью в редактировании кода, когда у вас возникнут проблемы с ним. И вам может повезти, и кто-то опубликует здесь код в качестве ответа. Forward Ed 5 лет назад 1
Достаточно ли для вас наличия флага, указывающего, что значение в листе 1 используется? Если да, то вспомогательная строка (или условное форматирование) с `COUNTIF` сделает эту работу. cybernetic.nomad 5 лет назад 1
@AlexR, я уверен, что вы используете диапазон данных из листа Sheet1 с идентификаторами для проверки в качестве источника на листе 2. В этом случае, если вы заблокируете этот диапазон данных в Sheet1, это поможет вам сделать то, что вы хотите сделать !! Rajesh S 5 лет назад 0

3 ответа на вопрос

0
Rob Gale

Как указал @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. Если это так, ячейка остается со старым значением, в противном случае новое значение восстанавливается.

Это хорошо работающий код, но с небольшим недостатком, если я ввожу новый Id в Sheet1 и добавлю тот же Id в Shee2, то после того, как этот код не позволит изменить или удалить вновь введенный Id в Sheet1. Но позволяет мне дублировать вновь введенный идентификатор в листе 1. Я думаю, что технически это никогда не должно происходить, потому что Лист 1 содержит уникальный список. Rajesh S 5 лет назад 0
Метод `Undo`, который вы используете, немного против паттерна. Это означает, что вы отменяете, а затем повторяете действия, когда в этом нет необходимости. Я только что проверил этот анти-шаблон в использовании, где я думаю (я не могу подтвердить), что он повредил рабочий лист. AJD 5 лет назад 0
0
AJD

Простое решение - использовать 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

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

Я думаю, что это хорошее решение VBA. Я избегал использования Worksheet_SelectionChange, потому что в прошлом у меня были проблемы, когда пользователь вставлял несколько ячеек. Из-за того, что вы блокируете страницу, ваше решение, похоже, позволяет избежать этой проблемы. Одно небольшое исправление: `Me.Protect UserInterfaceOnly = True` должно быть` Me.Protect UserInterfaceOnly: = True` Rob Gale 5 лет назад 0
@RobGale: Спасибо, исправил ":". Да, перетаскивание может быть проблемой, но я не прошел полный тест. Код, который я перерабатывал в своей обычной жизни, использует глобальную переменную для отслеживания режима вырезания / копирования, событие `_Change` для проверки ошибок проверки (а затем отмены) и` _SelectionChange` для предотвращения действий вырезания только на защищенных листах ( отменив действие вырезать, а не отменить). AJD 5 лет назад 0
0
Rob Gale

Вот альтернатива без 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).

Результат:

Похожие вопросы