Защита ячеек в excel vba
Многие при работе с таблицами любят их окрашивать в различные цвета по "категориям": красный - прогул; синий - больничный; желтый - коммандировка и т.д. и т.п. Это вполне может быть визуально наглядно и красиво, но в последствии хочется по подобной боевой раскраске осуществить с ячейками какие-то иные операции, чем просто полюбоваться. Например: подсчитать количество ячеек определенного цвета, отфильтровать по цвету и т.п. Сегодня я хочу рассказать, как можно защитить ячейки в зависимости от цвета заливки, шрифта. Один из случаев, когда закраска ячеек может быть оправдана - можно каким-то определенным цветом отметить ячейки с формулами, а потом их защитить кодом ниже.
В принципе это можно сделать руками: выделить все ячейки нужного цвета, правая кнопка мыши -Формат ячеек -Защита -Защищаемая ячейка. После чего защитить лист. Подробнее можно прочитать в статье Как разрешить изменять только выбранные ячейки? и в статье Защита листов и ячеек в MS Excel. Но если ячеек много, да еще они разбросаны по листу, то такой метод довольно утомителен. Однако это очень просто сделать при помощи кода ниже:
Как использовать код в своей книге: копируем код выше, в нужной книге переходим в редактор VBA( Alt + F11 ) -Insert -Module. В появившееся окно вставляем скопированный код.
Теперь выделяем все ячейки на листе, среди которых есть те, которые надо защитить -нажимаем сочетание клавиш Alt + F8 и выбираем из списка PrCellsByInterior , если надо защищать ячейки на основании цвета заливки или PrCellsByFont , если защищать надо в зависимости от цвета шрифта.
Защищать ячейки с конкретным цветом получилось, но что делать, если окрашены как раз те ячейки, защищать которые не надо? Да еще и ячейки окрашены разными цветами? Все просто: для защиты выбираем весь диапазон значимых ячеек, а в качестве ячейки-образца с заливкой указываем любую ячейку без заливки. Вот и все. Все ячейки без заливки будут защищены, а с заливкой - разрешены для ввода.
Tips_Macro_LockCellByColor.xls (48,0 KiB, 1 327 скачиваний)
Что важно знать: ячейки, цвет заливки или шрифта которых установлен при помощи условного форматирования, не будут защищены корректно. В этом случае код увидит реальную заливку, а не ту, что отражается.
выражения. Защита (Пароль , DrawingObjects , Содержимое , Сценарии , UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows , AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows, AllowSorting, AllowFiltering, AllowUsingPivotTables)
выражение Переменная, представляюная объект "Таблица".
Параметры
| Имя | Обязательный или необязательный | Тип данных | Описание |
|---|---|---|---|
| Password | Необязательный | Variant | Строка, которая указывает пароль, чувствительный к делу для таблицы или книги. Если этот аргумент опущен, можно отклонить таблицу или книгу без использования пароля. В противном случае необходимо указать пароль, чтобы отклонить таблицу или книгу. Если вы забудете пароль, вы не сможете отклонить таблицу или книгу. |
Используйте надежные пароли, содержащие строчные и прописные буквы, цифры и знаки. В ненадежных паролях не используются сочетания таких элементов. Надежный пароль: Y6dh!et5. Слабый пароль: House27. Длина паролей должна быть не меньше 8 символов. В парольной фразе лучше использовать 14 или более символов.
Примечания
В предыдущих версиях, если применить этот метод с аргументом UserInterfaceOnly, заданным для True, а затем сохранить книгу, при повторном возобновлении работы весь рабочий таблица (а не только интерфейс) будет полностью защищена. Чтобы повторно включить защиту пользовательского интерфейса после открытия книги, необходимо снова применить этот метод с помощью набора UserInterfaceOnly к True.
Если вы хотите внести изменения в защищенный таблицу, можно использовать метод Protect на защищенной таблице, если пароль предоставлен. Кроме того, другим методом было бы отклонить таблицу, внести необходимые изменения, а затем снова защитить таблицу.
Незащищенная означает, что ячейка может быть заблокирована (диалоговое окно Format Cells), но включена в диапазон, определенный в диалоговом окне Разрешить пользователям изменять диапазоны, и пользователь не защитил диапазон с помощью пароля или был проверен с помощью разрешений NT.
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
В Microsoft Excel обычно выбирается ячейка или ячейки, а затем выполняется действие, например форматирование ячеек или ввод значений. В Visual Basic обычно не требуется выбирать ячейки перед их изменением.
Например, чтобы ввести формулу в ячейку D6 с помощью Visual Basic, не нужно выбирать диапазон D6. Просто возвратите объект Range для этой ячейки, а затем присвойте свойству Formula нужную формулу, как показано в следующем примере.
Дополнительные сведения и примеры использования других методов для управления ячейками без их выбора см. в статье Ссылки на ячейки и диапазоны.
Использование метода Select и свойства Selection
Метод Select активирует листы и объекты на листах; свойство Selection возвращает объект, представляющий текущее выделение на активном листе в активной книге. Перед использованием свойства Selection необходимо активировать книгу, активировать или выбрать лист, а затем выбрать диапазон (или другой объект) с помощью метода Select.
Средство записи макросов часто создает макрос, использующий метод Select и свойство Selection. Следующая процедура Sub была создана с помощью средства записи макросов и показывает, как Select и Selection работают вместе.
В следующем примере выполняется та же задача без активации или выбора листа или ячеек.
Выбор ячеек на активном листе
Если для выбора ячеек используется метод Select, имейте в виду, что Select работает только на активном листе. Если выполнить процедуру Sub из модуля, метод Select завершится ошибкой, если процедура не активирует лист перед использованием метода Select для диапазона ячеек. Например, следующая процедура копирует строку из Листа1 в Лист2 в активной книге.
Активация ячейки в выделенном фрагменте
Метод Activate можно использовать для активации ячейки в выделенном фрагменте. Активной может быть только одна ячейка, даже если выделен диапазон ячеек. Следующая процедура выделяет диапазон и активирует ячейку в диапазоне, не изменяя выделенный фрагмент.
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
I have tried to use the code for ranges you have previously posted but it isn't working - can you advise me if I should be combining the code for ranges above or below?
I used the code provided to Carlos and it did exactly what I wanted it to. Is there a way to have some ranges offset within the ROW to the right of the protected range (as the "Carlos" code already does), but have other ranges offset within the COLUMN to the cell directly below the protected range? I tried entering the "Carlos" code twice and changing the offset, but I received a variety of errors.
Hi Charlie,Sorry can't help you with that yet. Thank you for your comment. Is there any function to set for example Row 2 cell 13 to 900? Or do i need to manually punch each cell name in the code? Bonjour,
Comment faire pour verrouiller de la cellule B8 à B10000?
D'avance merci de votre réponse.
Christophe
Thank you so much for this! It works perfectly.
I have used the code in Carlos' comment to run the macro automatically as you open the file. I was wondering if there is a way of having an "Undo" button or something like it, that allows you to cancel that code and therefore, unlock those ranges that have been locked.
I want those to be locked most of the time, but I would like to unlock them should I need to change any of the data. The only reason I am not protecting the whole sheet is because, if I do, it deactivates the ability to expand on tables. That in turn, expand on a line chart.
Thanks so much for the help!
Hi Erin,You can manually break the code by clicking the Break button in the Microsoft Visual Basic for Applications window to unlock those ranges. And run the code to activate it again. Thanks for your comment.
Is there anyway that this macro can be run automatically upon opening the file, or upon clicking any cell. I don't want people to have to run the macro manually in order for the required ranges to be protected.
Also, I will need to protect 12 non adjacent ranges. For example: I11:I20 and K11:K20 and M11:20 etc. How do I do this?
Last, and this one might be asking too much, but is it possible to apply the protection to the example ranges that I provided above, BUT then extend the protection down additional rows as new ones are added? In other words, protection would be applied to I11:I20 and K11:K20 and M11:20, but a user would be able to add a new row (row 21), with new data, but once the new row is added, then the protection would apply to I11:I21 and K11:K21 and M11:21. Am I asking for the moon? :-)
Thank you for everything that you've already provided! I can't thank you and other people like you enough for sharing the your knowledge. Amazing!
Dear Carlos,
The following VBA code can help you solving the problem. Please fill in your ranges in the fourth line of the code and press the Alt + Q keys to return to the worksheet. Then please shift to other worksheet and then go back to current sheet to activate the code. Thank you for your comment.
Dim xRg As Range
Private Sub Worksheet_Activate()
If xRg Is Nothing Then
Set xRg = Union(Range("I10:I20"), Range("K10:K20"), Range("M10:M20"), Range("O10:O20"))
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim I As Integer
Dim xRgNew As Range
Dim xRgLCell As Range
On Error Resume Next
Application.EnableEvents = False
For I = 1 To xRg.Areas.Count
Set xRgLCell = xRg.Areas.Item(I)
Set xRgLCell = xRgLCell(xRgLCell.Count).Offset(1, 0)
If Target.Address = xRgLCell.Address Then
If xRgNew Is Nothing Then
Set xRgNew = Target
Else
Set xRgNew = Union(xRgNew, Target)
End If
End If
Next
Set xRg = Union(xRg, xRgNew)
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo Exitsub
If (Not Intersect(xRg, Target) Is Nothing) And (Target.Count = 1) Then
Target.Offset(0, 1).Select
End If
Exitsub:
End Sub
You have provided me with a solution (half of it) that I have been struggling for the last few weeks, but I would need some more hints.
How this could be applied to a table range only, not whole worksheet? Thanks in advance.
Good Day,
As the VBA code provided below, please change the specified range "H:J,4:46" to the table range you only want to lock in the worksheet.
And cells(1,1) should be the cell outside the table range. When clicking on any cell in the table range, the cursor will be moved to that cell automatically.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim xRg As Range
Dim xRgEx As Range
Dim xRgExEach As Range
On Error Resume Next
Application.ScreenUpdating = False
Set xRg = Range("H:J,4:46") 'Change the row range and column range you will lock without protecting worksheet
Set xRgEx = Application.Intersect(xRg, Target)
If xRgEx Is Nothing Then Exit Sub
Cells(1, 1).Select 'Specify a cell you will shift to after selecting the locked cells
Application.ScreenUpdating = True
End Sub
Dear AJ,
If you want to lock a range of specified rows and columns, please try the below VBA script.
Читайте также: