Блокировать все в Excel, кроме значений

Question

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

Я пробовал "Защитить книгу", но не могу найти ни одной опции, которая позволила бы редактировать значения в заблокированных ячейках, сохраняя все остальное.

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

Как я могу заблокировать лист или написать какой-нибудь VBA, чтобы заблокировать все о ячейке, кроме ее значения? Я использую Excel 2016, но мои пользователи могут не быть. Мне нужно решение, которое будет работать в максимально старых версиях.

Мне известно, что "Специальная вставка" будет вставлять только значения, но я даю эту таблицу пользователям для заполнения и возврата, и я не уверен, что они будут понимать или вставлять "Специальная вставка" каждый раз, что приводит к мы пытаемся использовать недействительные данные.

Answer 1

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

В Интернете есть несколько дискуссий об обработке этого сценария, но ни один из них до сих пор не предоставил решение, которое действительно работает. Если ячейка не защищена, тогда в нее можно вставить что угодно.

Единственный способ, который работает, это обучить ваших пользователей.

Answer 2

VBA - действительно путь сюда.

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

Это даст вам код VBA для настройки защиты данных так, как должна быть ваша ячейка.

Теперь создайте макрос, который запускается при изменении ячейки. Вы можете использовать activecell, чтобы узнать, в какую ячейку вставлен пользователь, и выполнить процедуру настройки защиты данных в этой ячейке, чтобы исправить ее снова.

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

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

Answer 3

Другой подход будет таким.

Вам все еще нужен VBA для этого, но вы можете узнать, вставлен ли пользователь. Если так, отмените пасту, затем вставьте специальную вместо этого.

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

Ваш код будет выглядеть примерно так:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  Dim lastAction As String

  ' Get the last action performed by user
  lastAction = Application.CommandBars("Standard").Controls("&Undo").List(1)

  ' Check if the last action was a paste
  If Left(lastAction, 5) = "Paste" Then

    ' Perform an undo here

    ' Do a Paste special here

  End If
End Sub

см. также https://stackoverflow.com/questions/12525942/how-to-detect-paste-event-in-excel