Ограничить вставку в выпадающие ячейки в Excel

Question

У меня есть один лист Excel, который содержит много выпадающих столбцов. Допустим, в одном столбце значения Test1, Test2 и Test3 являются раскрывающимися. Ячейки под этим столбцом не должны принимать значения, отличные от этих 3. Если я скопирую и вставлю 'XYZ' в эти ячейки, он не должен принимать. Может ли кто-нибудь помочь мне, как справиться с этим?

Answer 1

Настройка проверки данных

Вы захотите использовать функцию проверки данных в Excel, чтобы ограничить ячейки определенным текстом. В вашем случае: "Test1", "Test2" и "Test3"

1. Выберите ячейки, которые должны принимать только значения "Test1", "Test2" и "Test3"

2. На вкладке Данные в группе Инструменты данных, нажмите на кнопку проверки данных.

   

3. В раскрывающемся списке Разрешить: выберите Список

4. В поле Источник: введите Test1, Test2, Test3

   

5. Нажмите кнопку ОК, чтобы применить изменения.

На этом этапе каждая ячейка, выбранная на шаге 1, будет иметь раскрывающийся список, связанный с ней. Пользователи электронной таблицы только будет в состоянии войти Test1, test2 или TEST3. Если они попытаются ввести что-то еще, они получат следующее сообщение об ошибке:

При желании вы можете настроить текст сообщения об ошибке, щелкнув вкладку « Предупреждение об ошибке » в шаге 4 сверху.

Запретить копирование / вставку данных проверенных ячеек

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

1. Выберите все ячейки, которые имеют проверку данных и назовите их DataValidationRange. Вы можете назвать диапазоны ячеек, введя имя слева от строки формул, где расположен адрес ячейки.
2. В модуле кода для рабочего листа (например, Sheet1) добавьте следующий код:

Private Sub Worksheet_Change(ByVal Target As Range)
    'Does the validation range still have validation?
    If HasValidation(Range("DataValidationRange")) Then
        Exit Sub
    Else
        Application.Undo
        MsgBox "Error: You cannot paste data into these cells." & _
        "Please use the drop-down to enter data instead.", vbCritical
    End If
End Sub

Private Function HasValidation(r) As Boolean
    'Returns True if every cell in Range r uses Data Validation
    On Error Resume Next
    x = r.Validation.Type
    If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function

К сожалению, VBA - единственный способ не допустить того, чтобы кто-то вставил над ячейками. Это было проблемой с проверкой данных в Excel с самого начала. Надеюсь, это поможет в любом случае.

Answer 2

Более простым решением было бы просто полностью отключить вставку в рабочую книгу. Я только что нашел решение для этого здесь:http://www.vbaexpress.com/kb/getarticle.php?kb_id=373