Ошибка «Unable to set the Value property» после защиты листа обычно означает, что защита действует и на интерфейс, и на макрос. Excel умеет разделить эти роли: `UserInterfaceOnly:=True` блокирует ручные изменения, но разрешает VBA работать с защищённым листом.
Есть важная ловушка: настройку `UserInterfaceOnly` нельзя считать постоянной после сохранения файла. Microsoft рекомендует применять её снова при открытии книги. Поэтому тест «сразу после запуска процедуры всё работает» недостаточен — книгу нужно закрыть, заново открыть и повторить изменение.
Сначала фиксируем, что именно защищено
| Свойство/условие | Что означает | Что проверить |
|---|---|---|
| cell.Locked = True | ячейка будет закрыта после Protect | нужны ли пользователю эти поля |
| sheet.ProtectContents | защита содержимого активна | когда и кем вызван Protect |
| UserInterfaceOnly = True | VBA может менять protected contents | применено ли после открытия |
| AllowFiltering = True | пользователь меняет существующий фильтр | AutoFilter создан до защиты |
| ошибка 1004 | операция запрещена или диапазон неверен | точная строка, sheet и range |
Флаг Locked сам по себе ничего не блокирует, пока лист не защищён. И наоборот, разрешение фильтра или сортировки не даёт макросу общего права на запись. Перед исправлением выведите имя листа, `ProtectContents`, адрес целевого Range и номер строки, где возникает ошибка.
Runtime-настройка живёт в Workbook_Open
Private Sub Workbook_Open()
Const DemoPassword As String = "replace-in-production"
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name = "Отчёт" Or ws.Name = "Данные" Then
ws.Protect Password:=DemoPassword, _
UserInterfaceOnly:=True, _
AllowFiltering:=True
End If
Next ws
End SubОбработчик размещается в модуле `ThisWorkbook`, а не в обычном Module. Пароль в примере демонстрационный: не вставляйте рабочий секрет в статью, лог или публичную книгу. Worksheet protection предназначена прежде всего для управления редактированием, а не как единственная граница защиты конфиденциальных данных.
Почему вчера работало, а утром перестало
Если процедуру Protect запускали вручную с `UserInterfaceOnly:=True`, макрос работает до закрытия книги. После нового запуска Excel лист остаётся защищённым, но VBA снова сталкивается с ограничением, пока `Workbook_Open` не применит режим. Сбой также появляется, если события отключены, макросы заблокированы политикой или открыта копия файла без нужного обработчика.
- Проверьте, сработал ли Workbook_Open, простым диагностическим логом без пароля.
- Не полагайтесь на ActiveSheet: используйте ThisWorkbook.Worksheets с явным именем.
- Не применяйте защиту ко всем листам, если часть из них управляется другой логикой.
- При AllowFiltering существующий AutoFilter создают до Protect.
- Проверяйте Windows и macOS Excel отдельно, если книга распространяется на обе платформы.
Временный Unprotect требует гарантированного возврата
Sub RebuildReport()
Const DemoPassword As String = "replace-in-production"
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Отчёт")
On Error GoTo Finally
ws.Unprotect Password:=DemoPassword
ws.Range("B2:B20").ClearContents
' controlled rebuild
Finally:
ws.Protect Password:=DemoPassword, _
UserInterfaceOnly:=True, AllowFiltering:=True
If Err.Number <> 0 Then Err.Raise Err.Number, Err.Source, Err.Description
End SubТакой fallback нужен только для операций, которые Excel всё равно запрещает при UI-only protection. Блок `Finally` здесь реализован меткой: защита возвращается и при ошибке. Но основной путь проще — применить UI-only один раз при открытии и не мигать состоянием защиты во время каждого расчёта.
Protect не должен менять смысл бизнес-операции
Защита листа не решает параллельные изменения, неверные формулы или запись не в ту книгу. Макрос сначала вычисляет целевой workbook/sheet/range, валидирует входные данные и только затем меняет ячейки. Не используйте `On Error Resume Next` вокруг массовой записи: он превращает частично обновлённый отчёт в «успешный».
| Симптом | Вероятная причина | Следующий тест |
|---|---|---|
| работает до перезапуска Excel | UI-only не переустановлен | Workbook_Open и события |
| один лист работает, другой нет | Protect вызван с разными параметрами | явный список листов |
| фильтр недоступен пользователю | нет AllowFiltering/AutoFilter | создать фильтр до Protect |
| макрос пишет не туда | ActiveSheet/ActiveWorkbook | ThisWorkbook и квалифицированный Range |
| после ошибки лист открыт | нет гарантированного reprotect | error handler/finally path |
Регрессионный тест начинается с закрытого Excel
- Открыть чистую копию xlsm и убедиться, что Workbook_Open выполнился.
- Попробовать изменить Locked-ячейку вручную: Excel должен запретить действие.
- Запустить макрос и сравнить только задуманный диапазон.
- Спровоцировать контролируемую ошибку в середине и проверить, что защита вернулась.
- Закрыть все окна Excel, открыть файл снова и повторить тест.
- Проверить фильтры, сортировку и ввод в специально разблокированные поля.
- Запустить книгу с отключёнными макросами и показать пользователю понятное ограничение.
Для разбора подготовьте минимальную копию без персональных данных, точную строку VBA, значение ProtectContents, место вызова Protect и последовательность после открытия. Рабочий пароль замените. Исправление завершено, когда пользователь не редактирует закрытые клетки, макрос меняет только разрешённый диапазон, а ошибка не оставляет лист открытым.