☰

Защита ЯЧЕЙКИ, но разрешение раскрывающихся вариантов

tag-icon • tag-icon microsoft-excel worksheet-function vba
Защита ЯЧЕЙКИ, но разрешение раскрывающихся вариантов

Я хочу защитить ячейку, но также разрешить пользователю выбирать параметры из раскрывающегося списка?

Я прочитал вопрос ниже, но не получил на него ответа.

Защита ячейки, но разрешение работы раскрывающегося списка

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

Есть ли способ заблокировать ЯЧЕЙКУ и включить защиту листа, но при этом разрешить выбор параметров списка проверки данных?

Я попробую любые варианты, включая VBA.

решение1

Возможно, есть способ сделать это лучше, но у меня есть идея, и, судя по моим тестам, она пока работает нормально.

Поэтому я использую Worksheet_SelectionChangeдля мониторинга того, что пользователь делает со списком.
Я просто разблокирую лист, когда выбрана ячейка, и блокирую его снова, когда выбрано что-то еще:
Обратите внимание, что мой список находится в N26.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("N26")) Is Nothing Then
    ActiveSheet.Unprotect
Else
    ActiveSheet.Protect
End If
End Sub

Проблема, очевидно, в том, что вы должны иметь возможность выделить ячейку, а затем вставить в нее что угодно.

Итак, нам нужно проверить, вставляет ли пользователь что-либо, и здесь я нашел кое-что интересное:https://stackoverflow.com/questions/27818152/excel-vba-как-определить-если-что-то-было-вставлено-в-рабочий-лист

Таким образом, мы можем проверить, было ли что-то вставлено, и отменить это.

Я собрал их вместе, изменил так, чтобы пользователь мог вставлять данные в любые незаблокированные ячейки, но не в N26.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("N26")) Is Nothing Then 'Data validation list adress
    ActiveSheet.Unprotect
Else
    ActiveSheet.Protect
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("N26")) Is Nothing Then
  Dim UndoList As String
  '~~> Get the undo List to capture the last action performed by user
  UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)

  '~~> Check if the last action was not a paste nor an autofill
  If Left(UndoList, 5) = "Paste" Then
        With Application
            .EnableEvents = False
            .Undo                  'Undo paste
            .EnableEvents = True
        End With
  End If
End If
End Sub

Обратите внимание, что некоторые из этих значений могут измениться, если вы не используете английскую версию Excel. Мне пришлось изменить "&Undo"и (UndoList, 5) = "Paste"на свой язык.

Думаю, вы также могли бы использовать это для своего следующего вопроса. Если вы снова разрешите вставку, но затем проверите каждое изменение для вставки, и если да, запустите IsNumeric(Target.value)проверку и отмените ее, если она не удалась.

Связанный контент