Макрос для выделения ячеек со снятой защитой на листе Excel

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

Выделение ячеек со снятой защитой макросом

Таблица шаблона отчета выглядит так:

Таблица шаблона.

По умолчанию в Excel нет инструмента для автоматического выделения незащищенных ячеек. А проверять установлена ли защита для каждой ячейки или нет – это потребует много сил и времени. По умолчанию каждая ячейка защищена при включении защиты листа. А чтобы снять защиту с ячейки нужно выбрать: «ГЛАВНАЯ»-«Формат»-«Формат ячеек». После перейти на закладку «Защита» и снять галочку на против опции «Защищаемая ячейка». Затем если включить защиту листа, выбрав инструмент: «РИЦЕНЗИРОВАНИЕ»-«Изменение»-«Защитить лист», то в незащищенную ячейку все равно можно вводить и редактировать значения данных. Поэтому лучшим решением будет – написать свой VBA макрос, который сам найдет и выделит каждую ячейку со снятой защитой в таблице Excel.

Для написания своего кода макроса открываем редактор Visual Basic (ALT+F11):

Sub SeeLock()
  Dim i As Long
  Dim diapaz1 As Range
  Dim diapaz2 As Range
Set diapaz1 = Application.Range(ActiveSheet.Range("A1"), _
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell))
   For i = 1 To diapaz1.Count
     If diapaz1(i).Locked = False Then
       If diapaz2 Is Nothing Then
       Set diapaz2 = diapaz1(i)
       Else
       Set diapaz2 = Application.Union(diapaz2, diapaz1(i))
       End If
     End If
   Next
If diapaz2 Is Nothing Then
MsgBox "Ненайдено ниодной ячейки!"
Else
  diapaz2.Select
  MsgBox "Найдено: " & diapaz2.Count & " ячеек!"
End If
End Sub
Visual Basic.

Затем в этом же редакторе создаем новый модуль выбрав инструмент: «Insert»-«Module». И в созданный модуль вводим код представленный ниже на листинге:

Модуль.

Теперь если мы хотим найти и выделить все незащищенные от редактирования ячейки в таблице отчета на листе с включенной защитой выберите инструмент: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«SeeLock»-«Выполнить». После запуска макроса выделиться несмежный диапазон всех незащищенных ячеек в таблице отчета.

Пример.

Основной принцип действия макроса основывается на тем, что с помощью цикла каждая ячейка проверяется установлено ли для ее свойства Locked (галочка – «защищаемая ячейка») значение False. Если да, значит ячейка незащищенная от редактирования, когда будет включена защита рабочего листа Excel. В таком случае ячейка со снятой защитой дополняет несмежный диапазон в переменной diapaz2.



Полезные советы:

  1. Если нужно сделать так чтобы этот макрос наоборот выделял только защищенные ячейки, тогда достаточно лишь изменить значение False на True.

    2. If diapaz1(i).Locked = True Then

  2. Если нужно выделить все ячейки, для которых отключена защита на целом листе, а не только в границах таблицы тогда сделает изменить код в строке, где описано создание экземпляра объекта в переменной diapaz1 на:

    3. Set diapaz1 = Selection

Примечание. При использовании второго полезного совета следует учитывать тот факт, что при таком коде нельзя выделять любые диапазоны листа перед запуском макроса. Иначе Excel будет очень медленно работать в процессе выполнения VBA кода.

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


en ru