Как предотвратить копирование и вставку в ячейки с раскрывающимся списком в Excel?
Раскрывающиеся списки проверки данных — это эффективный способ контролировать, какой тип информации можно ввести в определенные ячейки в Excel, что помогает поддерживать согласованность и целостность данных в ваших листах. Однако часто возникает проблема, когда вы копируете содержимое из одной ячейки и вставляете его в ячейку с раскрывающимся списком: проверка данных удаляется, и список исчезает. Это может привести к потере правил проверки данных и нарушению логики работы таблицы, особенно в случаях, когда пользовательский ввод должен быть ограничен вариантами из раскрывающегося списка.
Чтобы сохранить целостность ячеек с выпадающими списками, вы можете запретить пользователям вставлять данные поверх них. Это гарантирует, что все данные в этих важных полях продолжат следовать заданным ограничениям и параметрам. В этом руководстве вы узнаете, как эффективно остановить действия копирования и вставки в ячейки, содержащие раскрывающиеся списки, используя VBA. Этот метод подходит для форм, шаблонов с контролируемым вводом или любого листа, где качество данных имеет большое значение.
Предотвращение копирования и вставки в ячейке с раскрывающимся списком в Excel
Предотвращение копирования и вставки в ячейке с раскрывающимся списком в Excel
Чтобы защитить ваши раскрывающиеся списки от перезаписи скопированным содержимым, вы можете использовать VBA на листе, который содержит эти ячейки. Следуйте этим шагам:
1. Откройте лист, который содержит выпадающие списки, которые вы хотите защитить.
2. Нажмите Alt + F11, чтобы открыть окно Visual Basic для Applications.
3. В левой панели дважды щелкните лист, к которому вы хотите применить защиту (не «ThisWorkbook»). Вставьте следующий код VBA в редактор кода:
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. Попробуйте вставить содержимое в ячейку с выпадающим списком. Сообщение не позволит вам вставить, и выпадающий список останется нетронутым:
Примечания и ограничения:
Этот код VBA предназначен только для операций вставки в одну ячейку. Он может не предотвратить потерю проверки данных при вставке в несколько ячеек. Уведомите пользователей соответствующим образом.
Советы по устранению неполадок:
- Убедитесь, что код размещен в правильном модуле рабочего листа (не в общем модуле).
- Включите макросы при открытии файла.
- Тщательно проверьте наличие ошибок копирования/вставки при вставке кода.
- Протестируйте макрос на тестовом файле перед применением его к реальным данным.
Альтернативные решения:
- Защитите рабочий лист: Заблокируйте ячейки с выпадающими списками и защитите лист, чтобы предотвратить редактирование. Узнать больше
- Используйте оповещения проверки данных: Установите стиль оповещения на "Стоп", чтобы заблокировать недопустимый ручной ввод. Узнать больше
Легко создавайте выпадающие списки с флажками в Excel:
Утилита Выпадающий список с флажками из Kutools для Excel поможет вам легко создать выпадающий список с чекбоксами в указанном диапазоне, текущем рабочем листе, текущей книге или во всех открытых книгах в зависимости от ваших потребностей.
Скачайте полную версию 30-день бесплатно Kutools для Excel сейчас!
Связанные статьи:
Как создать выпадающий список с несколькими флажками в Excel?
Многие пользователи Excel стремятся создавать выпадающие списки с несколькими флажками для выбора нескольких элементов из списка за один раз. Фактически, вы не можете создать список с несколькими флажками с помощью проверки данных. В этом руководстве мы покажем вам два способа создания выпадающего списка с несколькими флажками в Excel. Это руководство предоставляет решение проблемы.
Создание выпадающего списка из другой книги в Excel
Довольно просто создать выпадающий список проверки данных между листами в рамках одной книги. Но если данные списка находятся в другой книге, что бы вы сделали? В этом руководстве вы узнаете, как создать выпадающий список из другой книги в Excel.
Создание поискового выпадающего списка в Excel
Для выпадающего списка с большим количеством значений найти нужное значение бывает непросто. Ранее мы представили метод автозаполнения выпадающего списка после ввода первой буквы. Кроме функции автозаполнения, вы также можете сделать выпадающий список поисковым для повышения эффективности работы при поиске подходящих значений в выпадающем списке. Для создания поискового выпадающего списка попробуйте метод в этом руководстве.
Автоматическое заполнение других ячеек при выборе значений в выпадающем списке Excel
Предположим, что вы создали выпадающий список на основе значений в диапазоне ячеек B8:B14. Когда вы выбираете любое значение в выпадающем списке, вы хотите, чтобы соответствующие значения в диапазоне C8:C14 автоматически заполнялись в выбранной ячейке. Для решения этой проблемы методы в данном руководстве окажут вам помощь.
Лучшие инструменты для повышения продуктивности в Office
Повысьте свои навыки работы в 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 и PowerPoint плюс Office Tab Pro
- Один установщик, одна лицензия — настройка занимает считанные минуты (MSI-совместимо)
- Совместная работа — максимальная эффективность между приложениями Office
- 30-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек