Перейти к содержимому

Kutools для Office — один пакет. Пять инструментов. Выполняйте больше.

Как предотвратить копирование и вставку в ячейки с раскрывающимся списком в Excel?

Author Siluvia Last modified

Раскрывающиеся списки проверки данных — это эффективный способ контролировать, какой тип информации можно ввести в определенные ячейки в Excel, что помогает поддерживать согласованность и целостность данных в ваших листах. Однако часто возникает проблема, когда вы копируете содержимое из одной ячейки и вставляете его в ячейку с раскрывающимся списком: проверка данных удаляется, и список исчезает. Это может привести к потере правил проверки данных и нарушению логики работы таблицы, особенно в случаях, когда пользовательский ввод должен быть ограничен вариантами из раскрывающегося списка.

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

Предотвращение копирования и вставки в ячейке с раскрывающимся списком в Excel


Предотвращение копирования и вставки в ячейке с раскрывающимся списком в Excel

Чтобы защитить ваши раскрывающиеся списки от перезаписи скопированным содержимым, вы можете использовать VBA на листе, который содержит эти ячейки. Следуйте этим шагам:

1. Откройте лист, который содержит выпадающие списки, которые вы хотите защитить.

2. Нажмите Alt + F11, чтобы открыть окно Visual Basic для Applications.

3. В левой панели дважды щелкните лист, к которому вы хотите применить защиту (не «ThisWorkbook»). Вставьте следующий код VBA в редактор кода:

copy and paste the code into the sheet module

VBA-код: Предотвращение вставки в раскрывающийся список

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice
    Dim xValue As String
    Dim xCheck1 As String
    Dim xCheck2 As String
    Dim xRg As Range
    Dim xArrCheck1() As String
    Dim xArrCheck2() As String
    Dim xArrValue()
    Dim xCount, xJ As Integer
    Dim xBol As Boolean
'    If Target.Count > 1 Then
'        Exit Sub
'        End If
    xCount = Target.Count
    ReDim xArrCheck1(1 To xCount)
    ReDim xArrCheck2(1 To xCount)
    ReDim xArrValue(1 To xCount)
    Application.EnableEvents = False
    On Error Resume Next
    xJ = 1
    For Each xRg In Target
        xArrValue(xJ) = xRg.Value
         xArrCheck1(xJ) = xRg.Validation.InCellDropdown
        xJ = xJ + 1
    Next

    Application.Undo

    xJ = 1
    For Each xRg In Target
        xArrCheck2(xJ) = xRg.Validation.InCellDropdown
        xJ = xJ + 1
    Next

    xBol = False
    For xJ = 1 To xCount
        If xArrCheck2(xJ) <> xArrCheck1(xJ) Then
            xBol = True
            Exit For
        End If
    Next

    If xBol Then
       MsgBox "The selected cells containg data validation drop-down lists, no pasting allowed."
    Else
        xJ = 1
        For Each xRg In Target
            xRg.Value = xArrValue(xJ)
            xJ = xJ + 1
        Next

    End If

    Application.EnableEvents = True
End Sub

4. Нажмите Alt + Q, чтобы закрыть редактор VBA и вернуться в Excel.

5. Попробуйте вставить содержимое в ячейку с выпадающим списком. Сообщение не позволит вам вставить, и выпадающий список останется нетронутым:

Paste blocked dialog

Примечания и ограничения:
Этот код VBA предназначен только для операций вставки в одну ячейку. Он может не предотвратить потерю проверки данных при вставке в несколько ячеек. Уведомите пользователей соответствующим образом.

Советы по устранению неполадок:

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

Альтернативные решения:

  • Защитите рабочий лист: Заблокируйте ячейки с выпадающими списками и защитите лист, чтобы предотвратить редактирование. Узнать больше
  • Используйте оповещения проверки данных: Установите стиль оповещения на "Стоп", чтобы заблокировать недопустимый ручной ввод. Узнать больше

Легко создавайте выпадающие списки с флажками в Excel:

Утилита Выпадающий список с флажками из Kutools для Excel поможет вам легко создать выпадающий список с чекбоксами в указанном диапазоне, текущем рабочем листе, текущей книге или во всех открытых книгах в зависимости от ваших потребностей.
Скачайте полную версию 30-день бесплатно Kutools для Excel сейчас!


Связанные статьи:

Как создать выпадающий список с несколькими флажками в Excel?
Многие пользователи Excel стремятся создавать выпадающие списки с несколькими флажками для выбора нескольких элементов из списка за один раз. Фактически, вы не можете создать список с несколькими флажками с помощью проверки данных. В этом руководстве мы покажем вам два способа создания выпадающего списка с несколькими флажками в Excel. Это руководство предоставляет решение проблемы.

Создание выпадающего списка из другой книги в Excel
Довольно просто создать выпадающий список проверки данных между листами в рамках одной книги. Но если данные списка находятся в другой книге, что бы вы сделали? В этом руководстве вы узнаете, как создать выпадающий список из другой книги в Excel.

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

Автоматическое заполнение других ячеек при выборе значений в выпадающем списке Excel
Предположим, что вы создали выпадающий список на основе значений в диапазоне ячеек B8:B14. Когда вы выбираете любое значение в выпадающем списке, вы хотите, чтобы соответствующие значения в диапазоне C8:C14 автоматически заполнялись в выбранной ячейке. Для решения этой проблемы методы в данном руководстве окажут вам помощь.

Больше учебников для выпадающих списков...

Лучшие инструменты для повышения продуктивности в Office

🤖 Kutools AI Aide: Совершенно новый подход к анализу данных благодаря: Интеллектуальное выполнение |  Генерация кода  |  Создание пользовательских формул |  Анализ данных и построение диаграмм  |  Вызов Kutools Functions
Популярные функции: Поиск, выделение или отметка дубликатов | Удалить пустые строки | Объединить столбцы или адреса без потери данных | Округлить ...
Супер ПОИСК: VLOOKUP по нескольким критериям | VLOOKUP по нескольким значениям | Многолистовой поиск | Распознавание нечетких соответствий ...
Расширенный раскрывающийся список: Быстро создать раскрывающийся список | Зависимый раскрывающийся список | Множественный выбор в раскрывающемся списке ...
Менеджер столбцов: Добавить определённое количество столбцов | Переместить столбцы | Переключить видимость скрытых столбцов | Сравнить диапазоны и столбцы ...
Рекомендуемые функции: Сетка фокусировки | Дизайн листа | Улучшенная строка формулы | Управление книгой и листами | Библиотека автотекста | Выбор даты | Объединить данные | Зашифровать/расшифровать ячейки | Отправить письмо по списку | Супер фильтр | Специальный фильтр (фильтр жирный/курсив/зачеркнутый...) ...
Топ15 наборов инструментов:12 текстовых инструментов (Добавить текст, Удалить определенные символы, ...) |50+ типов диаграмм (Диаграмма Ганта, ...) |40+ полезных формул (Расчет возраста на основе даты рождения, ...) |19 инструментов для вставки (Вставить QR-код, Вставить изображение по пути, ...) |12 инструментов преобразования (Преобразовать в слова, Конвертация валюты, ...) |7 инструментов объединения и разделения (Расширенное объединение строк, Разделить ячейки, ...) | ... и многое другое
Используйте Kutools на предпочитаемом вами языке — поддерживает Английский, Испанский, Немецкий, Французский, Китайский и более40 других языков!

Повысьте свои навыки работы в Excel с помощью Kutools для Excel и ощутите эффективность на новом уровне. Kutools для Excel предлагает более300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы выбрать функцию, которая вам нужнее всего...


Office Tab добавляет вкладки в Office и делает вашу работу намного проще

  • Включите режим вкладок для редактирования и чтения в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
  • Открывайте и создавайте несколько документов во вкладках одного окна вместо новых отдельных окон.
  • Увеличьте свою продуктивность на50% и уменьшите количество щелчков мышью на сотни ежедневно!

Все надстройки Kutools. Один установщик

Пакет Kutools for Office включает надстройки для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro — идеально для команд, работающих в разных приложениях Office.

Excel Word Outlook Tabs PowerPoint
  • Комплексный набор — надстройки для Excel, Word, Outlook и PowerPoint плюс Office Tab Pro
  • Один установщик, одна лицензия — настройка занимает считанные минуты (MSI-совместимо)
  • Совместная работа — максимальная эффективность между приложениями Office
  • 30-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
  • Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек