Как защитить лист Excel и одновременно оставить отдельные ячейки редактируемыми — VBA-макрос который делает это автоматически, с разбором кода и кнопкой запуска.
Защитите лист Excel но оставьте нужные ячейки открытыми — макрос за 10 строк
Вы создали таблицу. Формулы выверены, структура выстроена, всё работает как надо. Отправляете коллеге — и через час получаете файл обратно с испорченными формулами, сдвинутыми заголовками и удалёнными строками. Человек не хотел навредить. Просто кликнул не туда.
Стандартное решение — защита листа. Рецензирование → Защитить лист → пароль → ОК. Теперь никто ничего не сломает. Но теперь и вы сами не можете редактировать файл без снятия защиты. А если нужно чтобы коллега мог вводить данные в определённые ячейки, но не трогал всё остальное — стандартная защита с этим справляется плохо.
Ручной способ существует: выделить нужные ячейки, снять с них флаг «Защищаемая ячейка» в формате ячеек, потом включить защиту листа. Работает. Но если таких ячеек двадцать разбросанных по всему листу, а листов в книге пять — это час работы каждый раз при изменении структуры.
Макрос делает то же самое за секунду. Вы указываете какие ячейки должны остаться открытыми, запускаете — и лист защищён с точными исключениями. Меняется структура — меняете список в коде и запускаете снова.
В этой статье разберём как работает защита листа в Excel изнутри, напишем макрос с нуля, добавим несколько полезных версий под разные задачи и прикрепим кнопку запуска прямо на лист.
Как защита листа работает изнутри
Прежде чем писать код — важно понять механику. Защита листа в Excel работает не так как большинство думает.
Каждая ячейка в Excel по умолчанию имеет свойство «Защищаемая ячейка» — оно включено. Но это свойство ничего не делает пока защита листа выключена. Оно как замок на двери без двери — существует, но не работает.
Когда вы включаете защиту листа, Excel смотрит на каждую ячейку: свойство «Защищаемая ячейка» включено? Если да — ячейка блокируется. Если нет — остаётся редактируемой.
Значит логика работы такая: сначала снять флаг «Защищаемая ячейка» с ячеек которые должны остаться открытыми, потом включить защиту листа. После этого защищённые ячейки нельзя трогать, незащищённые — можно редактировать свободно.
Именно это и делает макрос. Автоматически, по вашему списку, за одно нажатие кнопки.
Подготовка к написанию кода
Два стандартных условия для работы с макросами.
Макросы должны быть включены. Файл → Параметры → Центр управления безопасностью → Параметры макросов → Включить все макросы. Без этого код не запустится.
Формат файла — .xlsm. Файлы с макросами сохраняются только в этом формате. Если попробуете сохранить как .xlsx — Excel предупредит что макрос будет удалён.
Открыть редактор VBA: Alt+F11. Откроется отдельное окно. В левой панели правая кнопка на любом листе или на «Эта книга» → Вставить → Модуль. Появится пустое поле для кода.
Простая версия — защита с фиксированным списком ячеек
Начнём с базового варианта. Макрос снимает защиту с указанных диапазонов и включает защиту листа. Всё остальное блокируется.
Sub ЗащититьЛистСИсключениями()
Dim ws As Worksheet*
Set ws = ActiveSheet*
' Снимаем защиту если она уже стоит*
ws.Unprotect Password:="ВашПароль"*
' Сначала блокируем все ячейки*
ws.Cells.Locked = True*
' Указываем ячейки которые должны остаться открытыми*
ws.Range("B2:B10").Locked = False*
ws.Range("D2:D10").Locked = False*
ws.Range("F5").Locked = False*
' Включаем защиту листа*
ws.Protect Password:="ВашПароль", _*
DrawingObjects:=True, _*
Contents:=True, _*
Scenarios:=True, _*
AllowFormattingCells:=False, _*
AllowFormattingColumns:=False, _*
AllowFormattingRows:=False, _*
AllowInsertingColumns:=False, _*
AllowInsertingRows:=False, _*
AllowDeletingColumns:=False, _*
AllowDeletingRows:=False, _*
AllowSorting:=False, _*
AllowFiltering:=True*
MsgBox "Лист защищён. Открытые ячейки: B2:B10, D2:D10, F5", vbInformation*
End Sub
Замените «ВашПароль» на нужный пароль или уберите параметр Password полностью если пароль не нужен. Диапазоны в строках с .Locked = False замените на те которые нужны в вашем файле.
Что происходит в каждой строке кода
Разбираем по блокам — чтобы вы понимали логику и могли адаптировать код под любую задачу.
ws.Unprotect Password:="ВашПароль" — снимаем защиту перед изменениями. Если лист уже был защищён и макрос запускается повторно — без этой строки код выдаст ошибку. Строка выполняется безопасно даже если защиты нет.
ws.Cells.Locked = True — блокируем абсолютно все ячейки листа. Это важный шаг. Без него ячейки которые кто-то вручную разблокировал раньше останутся открытыми. Начинаем с чистого состояния — всё заблокировано.
ws.Range("B2:B10").Locked = False — снимаем блокировку с конкретного диапазона. После включения защиты эти ячейки будут редактируемыми. Таких строк может быть сколько угодно — по одной на каждый диапазон.
ws.Protect Password:="ВашПароль" — включаем защиту листа с подробными настройками. Каждый параметр AllowXxx управляет тем что пользователь может делать на защищённом листе. AllowFiltering:=True оставлен включённым — фильтры часто нужны даже на защищённых листах. Остальные операции заблокированы.
Версия с динамическим списком — менять диапазоны без правки кода
Если открытые диапазоны меняются часто — удобнее хранить их прямо в таблице на отдельном листе. Макрос читает список из ячеек и применяет автоматически.
Создайте лист «Настройки». В столбце A напишите диапазоны которые должны остаться открытыми — по одному в строке: B2:B10, D2:D10, F5, и так далее.
Sub ЗащититьДинамически()
Dim ws As Worksheet*
Dim wsНастройки As Worksheet*
Dim lastRow As Long*
Dim i As Long*
Dim диапазон As String*
Set ws = ActiveSheet*
Set wsНастройки = Sheets("Настройки")*
ws.Unprotect Password:="ВашПароль"*
ws.Cells.Locked = True*
lastRow = wsНастройки.Cells(wsНастройки.Rows.Count, 1).End(xlUp).Row*
For i = 1 To lastRow*
диапазон = Trim(wsНастройки.Cells(i, 1).Value)*
If диапазон <> "" Then*
On Error Resume Next*
ws.Range(диапазон).Locked = False*
On Error GoTo 0*
End If*
Next i*
ws.Protect Password:="ВашПароль", _*
Contents:=True, _*
AllowFiltering:=True*
MsgBox "Лист защищён. Применено диапазонов: " & lastRow, vbInformation*
End Sub
Теперь чтобы изменить список открытых ячеек — достаточно отредактировать лист «Настройки» и запустить макрос заново. В код лезть не нужно. Это особенно удобно если файлом пользуются несколько человек и список диапазонов периодически меняется.
On Error Resume Next и On Error GoTo 0 — защита от ошибки при некорректно введённом диапазоне. Если в ячейке «Настроек» написано что-то что не является адресом диапазона — макрос просто пропустит эту строку и продолжит работу.
Версия для нескольких листов сразу
Когда в книге несколько листов с одинаковой структурой — защищать каждый вручную долго. Макрос обходит все листы и применяет одинаковые настройки защиты.
Sub ЗащититьВсеЛисты()
Dim ws As Worksheet*
Dim skipSheets As String*
' Листы которые не нужно защищать*
skipSheets = ",Настройки,Справочник,Сводный,"*
For Each ws In ThisWorkbook.Sheets*
If InStr(skipSheets, "," & ws.Name & ",") = 0 Then*
ws.Unprotect Password:="ВашПароль"*
ws.Cells.Locked = True*
' Открытые диапазоны — одинаковые для всех листов*
ws.Range("B2:B100").Locked = False*
ws.Range("E2:E100").Locked = False*
ws.Protect Password:="ВашПароль", _*
Contents:=True, _*
AllowFiltering:=True*
End If*
Next ws*
MsgBox "Все листы защищены.", vbInformation*
End Sub
Переменная skipSheets содержит имена листов которые не нужно трогать. Перечислите их через запятую — и макрос пропустит их при обходе книги. Это защищает служебные листы от случайной блокировки.
Макрос для снятия защиты — обратная операция
Защиту нужно периодически снимать — для редактирования структуры или обновления формул. Добавим макрос который снимает защиту со всех листов сразу.
Sub СнятьЗащитуВсехЛистов()
Dim ws As Worksheet*
Dim снято As Long*
снято = 0*
For Each ws In ThisWorkbook.Sheets*
On Error Resume Next*
ws.Unprotect Password:="ВашПароль"*
If Err.Number = 0 Then снято = снято + 1*
On Error GoTo 0*
Next ws*
' Разблокируем все ячейки*
For Each ws In ThisWorkbook.Sheets*
ws.Cells.Locked = False*
Next ws*
MsgBox "Защита снята с " & снято & " листов.", vbInformation*
End Sub
После запуска этого макроса все листы открыты для редактирования, все ячейки разблокированы. Когда закончили изменения — запускаете макрос защиты и всё возвращается на место.
Если вы регулярно работаете с Excel и хотите автоматизировать рутинные задачи — подписывайтесь на Telegram-канал. Там каждый день короткие разборы, готовые макросы и файлы для скачивания: t.me/macroschannel
А если интересно как Excel тормозит из-за неправильных формул и структуры — читайте 3 формулы Excel, которые ломают таблицы — проверьте свои файлы. Там разобраны типичные ошибки которые замедляют даже простые файлы.
Как привязать макросы к кнопкам
Два макроса — защита и снятие защиты — удобно разместить прямо на листе в виде кнопок. Один клик защищает, другой открывает.
Перейдите на вкладку «Разработчик». Нажмите «Вставить» → в разделе «Элементы управления формы» выберите «Кнопка». Нарисуйте кнопку на листе — зажмите мышь и растяните прямоугольник в нужном месте.
Сразу после того как отпустите кнопку мыши, появится диалог «Назначить макрос». Выберите ЗащититьЛистСИсключениями — и нажмите ОК. Переименуйте кнопку: правая кнопка мыши → «Изменить текст» → напишите «Защитить лист».
Повторите то же самое для второй кнопки — назначьте на неё СнятьЗащитуВсехЛистов, назовите «Снять защиту».
Теперь любой пользователь файла — даже не знающий VBA — может в один клик включить или выключить защиту. Это особенно ценно когда файл передаётся между несколькими людьми и нужно чтобы каждый мог работать со своей частью не ломая чужую.
Параметры защиты — что можно разрешить пользователям
Когда лист защищён, по умолчанию пользователь не может делать почти ничего кроме выделения ячеек. Но через параметры Allow можно тонко настроить что разрешено.
AllowFormattingCells:=True — пользователь может менять формат ячеек: шрифт, цвет, границы. Полезно если нужно дать свободу оформления при заблокированном содержимом.
AllowFormattingColumns:=True и AllowFormattingRows:=True — можно менять ширину столбцов и высоту строк. Удобно когда таблица используется для просмотра отчётов — пользователь подгоняет размеры под экран.
AllowInsertingRows:=True — можно вставлять строки. Нужно осторожно: новые строки могут нарушить диапазоны формул.
AllowDeletingRows:=True — можно удалять строки. Ещё более рискованный параметр — только если вы уверены что пользователь понимает что делает.
AllowSorting:=True — можно сортировать. Безопасно если данные не связаны формулами с фиксированными позициями.
AllowFiltering:=True — можно фильтровать. Практически всегда стоит оставить включённым — фильтры не меняют данные и нужны большинству пользователей.
AllowUsingPivotTables:=True — можно работать со сводными таблицами. Нужно если на листе есть сводная и пользователь должен с ней взаимодействовать.
Комбинируйте эти параметры под конкретную задачу. Универсального рецепта нет — зависит от того кто будет пользоваться файлом и что именно должен делать.
Частые ошибки при работе с защитой
Ошибка «Лист защищён» при запуске макроса. Макрос пытается изменить ячейки на уже защищённом листе. Убедитесь что строка ws.Unprotect стоит до любых операций с ячейками и содержит правильный пароль.
Ячейки остаются заблокированными несмотря на .Locked = False. Скорее всего забыли снять защиту листа перед изменением свойств ячеек. Свойство Locked можно изменять только на незащищённом листе.
Макрос выдаёт ошибку при неправильном пароле. Если пароль не совпадает — Unprotect выдаёт ошибку. Добавьте обработчик: On Error Resume Next перед ws.Unprotect, On Error GoTo 0 после. Тогда при неверном пароле макрос пропустит лист и продолжит работу.
После защиты нельзя использовать автофильтр. Убедитесь что параметр AllowFiltering:=True включён в строке ws.Protect. По умолчанию он выключен.
Защита слетает при открытии файла на другом компьютере. Это не баг — защита листа в Excel не шифрует файл. Она лишь блокирует интерфейс. Пользователь с техническими знаниями может обойти её. Если нужна реальная защита данных — используйте шифрование файла через «Файл → Сведения → Защита книги → Зашифровать паролем».
Как проверить что защита работает правильно
После запуска макроса всегда проверяйте результат вручную — три простых теста.
Первый тест: кликните на ячейку которая должна быть заблокирована и попробуйте что-то написать. Должно появиться сообщение о защите листа.
Второй тест: кликните на ячейку которая должна быть открытой и введите любое значение. Оно должно ввестись без ошибок.
Третий тест: попробуйте удалить строку или вставить столбец. Если параметры AllowDeletingRows и AllowInsertingColumns выключены — операция должна быть заблокирована.
Если что-то работает не так — вернитесь в редактор VBA, снимите защиту макросом, проверьте список диапазонов и запустите снова. Обычно причина либо в неправильно указанном диапазоне, либо в том что строка ws.Cells.Locked = True пропущена.
Связанные материалы по Excel и VBA
Защита листа — одна из задач которые решаются быстрее с помощью макросов. Если хотите углубиться в автоматизацию Excel — начните с базовых вещей. В статье 5 приёмов Excel, которые экономят 5 часов в неделю разобраны практические инструменты которые используются каждый день. А почему Excel начинает тормозить и как это исправить — в материале Почему Excel тормозит — 10 причин и быстрые способы ускорить файл.
Если работаете с формулами подстановки — статья ИНДЕКС + ПОИСКПОЗ в Excel: профессиональная подстановка данных покажет как делать гибкие поиски которые ВПР не умеет. А для подготовки к собеседованиям по Excel — НЕ БЕРУТ НА РАБОТУ, ЕСЛИ НЕ ЗНАЕШЬ ЭТОГО: ВПР В EXCEL БЕЗ ИЛЛЮЗИЙ и ПОИСКПОЗ в Excel — как находить строки, управлять подстановками и проходить собеседования.
Подписывайтесь на Telegram — там короткие гайды, готовые файлы и разборы реальных задач по Excel и VBA каждый день: t.me/macroschannel
Итог
Защита листа с исключениями решает конкретную задачу: одни ячейки редактируются, другие заблокированы. Стандартный инструмент Excel с этим справляется, но требует ручной настройки каждой ячейки. Макрос автоматизирует весь процесс — один запуск и структура защищена точно по вашим правилам.
Четыре версии кода в статье закрывают разные сценарии: фиксированный список диапазонов, динамический список из отдельного листа, защита нескольких листов сразу, снятие защиты одной кнопкой. Начните с простой версии, протестируйте на копии файла — и адаптируйте под свою задачу.
Главное правило: всегда тестируйте защиту после запуска макроса. Три клика — и вы убедитесь что всё работает именно так как задумано.