Запрет на редактирование ячейки vba excel
Обновлено: 07.07.2024
Иногда бывает полезно защитить данные на листе от изменений другими пользователями, но при этом так же надо будет работать с данными на листе из VBA(т.е. вносить изменения с помощью кода). Обычная защита листа, конечно, подходит, хоть и есть небольшой недостаток: надо перед каждым обращением к листу снимать с него защиту, выполнять необходимые действия и защищать заново:
Но есть метод проще.
Если выполнить ниже приведенную строчку кода, то пользователю невозможно будет изменить данные на листе(кроме тех, которые Вы сами разрешите), однако код VBA(макрос) сможет преспокойно вносить любые изменения, не снимая защиту.
Sub Protect_for_User_Non_for_VBA() ActiveSheet.Protect Password:="1111", UserInterfaceOnly:=True End Sub
Основную роль здесь играет параметр UserInterfaceOnly . Если его установить в True , то это говорит Excel-ю, что коды VBA могут выполнять действия по изменению ячеек, не снимая защиты методом Unprotect. Однако сама защита листа при этом не снимается и вручную изменить данные ячеек, не сняв защиту с листа, невозможно.
Код выше устанавливает такую защиту только на активный лист книги. Но можно указать лист явно(например установить защиту на лист с именем Лист1 в активной книге и лист, идущий вторым по порядку в книге( Sheets(2) )):
Sub Protect_for_User_Non_for_VBA() Sheets(2).Protect Password:="1111", UserInterfaceOnly:=True Sheets("Лист1").Protect Password:="1111", UserInterfaceOnly:=True End Sub
Так же приведенный код можно еще чуть модернизировать и разрешить пользователю помимо изменения ячеек еще и использовать автофильтр:
Sub Protect_for_User_Non_for_VBA() Sheets(2).Protect Password:="1111", UserInterfaceOnly:=True 'на лист "Лист1" поставим защиту и разрешим пользоваться фильтром Sheets("Лист1").Protect Password:="1111", AllowFiltering:=True, UserInterfaceOnly:=True End Sub
Можно разрешить и другие действия(выделение незащищенных ячеек, выделение защищенных ячеек, форматирование ячеек, вставку строк, вставку столбцов и т.д. Чуть подробнее про доступные параметры можно узнать в статье Защита листов и ячеек в MS Excel). А как будет выглядеть строка кода с разрешенными параметрами можно узнать, записав макрорекордером установку защиты листа с нужными параметрами:
После этого получится строка вроде такой:
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowFiltering:=True
здесь я разрешил использовать автофильтр( AllowFiltering:=True ), вставлять строки( AllowInsertingRows:=True ) и столбцы( AllowInsertingColumns:=True ).Чтобы добавить возможность изменять данные ячеек только через код VBA, останется добавить параметр UserInterfaceOnly:=True:
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowFiltering:=True, UserInterfaceOnly:=True
и так же неплохо бы добавить и пароль для снятия защиты, т.к. запись макрорекордером не записывает пароль:
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowFiltering:=True, UserInterfaceOnly:=True, Password:="1111"
Этот метод всем хорош, все отлично, но. Параметр UserInterfaceOnly сбрасывается сразу после закрытия книги. Т.е. если установить таким образом защиту на лист и закрыть книгу, то при следующем открытии защиты этой уже не будет - останется лишь стандартная защита. Поэтому, если необходимо такую защиту видеть постоянно, то данный макрос лучше всего прописывать на событие открытия книги(модуль ЭтаКнига(ThisWorkbook)).
Сделать это можно таким кодом:
Private Sub Workbook_Open() Sheets("Лист1").Protect Password:="1111", UserInterfaceOnly:=True End Sub
Этот код сработает только после того, как книга будет открыта. А это значит, чтобы увидеть результат необходимо после записи этого кода в ЭтаКнига сохранить книгу, закрыть её и открыть заново. Тогда в сам момент открытия книги код сработает и установит на "Лист1" правильную защиту.
Часто так же бывает необходимо устанавливать одинаковую защиту на все листы книги. Сделать это можно таким кодом, который так же должен быть размещен в модуле ЭтаКнига(ThisWorkbook):
Private Sub Workbook_Open() Dim wsSh As Object For Each wsSh In Me.Sheets Protect_for_User_Non_for_VBA wsSh Next wsSh End Sub Sub Protect_for_User_Non_for_VBA(wsSh As Worksheet) wsSh.Protect Password:="1111", UserInterfaceOnly:=True End Sub
Плюс во избежание ошибок лучше перед установкой защиты снимать ранее установленную(если она была):
Sub Protect_for_User_Non_for_VBA(wsSh As Worksheet) wsSh.Unrotect "1111" wsSh.Protect Password:="1111", UserInterfaceOnly:=True End Sub
Ну и если надо такую защиту установить только на конкретные листы, то убираем цикл и вызываем процедуру только для нужных листов. Если известны их имена, то можно прибегнуть к использованию массивов:
Private Sub Workbook_Open() Dim arr, sSh arr = Array("Отчет", "База", "Бланк") For Each sSh in arr Protect_for_User_Non_for_VBA Me.Sheets(sSh) Next End Sub Sub Protect_for_User_Non_for_VBA(wsSh As Worksheet) wsSh.Protect Password:="1111", AllowFiltering:=True, UserInterfaceOnly:=True End Sub
Для применения в своих задачах в данном коде необходимо лишь изменить(добавить, удалить, вписать другие имена) имена листов в этой строке: Array("Отчет", "База", "Бланк")
Примечание: Метод защиты через UsefInterface всем хорош, но есть одно ограничение: метод невозможно использовать в книге с общим доступом(Рецензирование -Доступ к книге), т.к. при общем доступе существуют ограничения, среди которых и такое, которое запрещает изменять параметры защиты для книги в общем доступе.
Статья помогла? Поделись ссылкой с друзьями!
ВидеоурокиПоиск по меткам
Всем Добрый день, очень странный момент. Excel 2003. Если устанавливаю значение EnableSelection через код:
ActiveSheet.EnableSelection = xlUnlockedCells
То при сохранении файла и повторном открытии значение данного параметра скидывается. Все ячейки доступны для выбора.
Если же это делаю через интерфейс excel(сервис ->установить защиту) то все нормально. После закрытия/открытия всё сохраняется.
Пробовал записать макроркордером установку защиты, ну в общем то он тоже самое и записывает:
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
Так же пробую именно этим кодом установить недоступность для выделения, свойство устанавливается, но после сохранение/закрытия/повторного открытия так же сбрасывается на ноль (все ячейки доступны для выделения).
В excel 2007 таких проблем нет. Всё корректно сохраняется.
Может быть у кого то есть мысли по данной ситуации? Благодарю за помощь.
Нашел ответ, может кому пригодиться. Данный баг возникает в локализованной версии excel 2003 без сервис пака. Установка SP3 решила проблему. Нашел пользователей с подобной проблемой на другом ресурсе.
А как можно защитить только отдельные листы из всей книги?
Влад, вызывайте процедуру защиты(Protect_for_User_Non_for_VBA) несколько раз, передавая в неё нужный лист по очереди. Или цикл по листам с проверкой на имя. Все зависит от ситуации и имен листов.
Добрый день! Подскажи Пробовал защитить первый лист по вышеописанной процедуре в котором есть таблица, собираются данные с других листов книги макросом записанным в первом листе. - не работает, пропадают данные с первого листа ( сводного ), но если снять защиту с листа первого, и сделать запись в др листах книги, то таблица отображается в первозданном виде. В первом листе и на др листах шапки таблицы одинаковые, есть выпадающиеся списки, в отдельных столбцах, - в других прописаны разные формулы. Пробовал записать на первый лист код в другой книге на чистом листе все работает.
Private Sub Workbook_Open()
'включаем защиту первого листа для пользователя, но не макроса
Worksheets("Лист1").Protect Password:="123", UserInterfaceOnly:=True
End Sub
Помогите решить проблему за ранее спасибо!
Подскажите, почему данный подход не работает, когда пытаюсь записывать изменения в ячейке в комментарий. на строке AddComment выдает ошибку, лист защищен. Но я же вношу комментарий через VBA и по идее защита не должна сработать. Причем значение в ячейку вносится, а вот комментарий уже нет.
Потому что комментарии - это объекты. И они несколько отдельный элемент защиты. При установке защиты разрешите изменение объектов и все будет работать.
Дмитрий, дорый день, благодарю за полезную информацию, а подскажите, пож-та, возможно ли сделать так, чтобы защита работала следующим образом: формулы были скрыты, но при этом оставалась возможность не только добавления столбцов и работы с группированными данными, но и возможность удаления столбцов? или это невозможно решить в рамках VBA? не могу найти подходящий макрос в интернете(
Option Explicit Private Sub Workbook_Open() Dim wsSh As Worksheet Set wsSh = Me.Sheets("рынок") wsSh.EnableOutlining = True wsSh.Protect Password:="978", UserInterfaceOnly:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True End Sub
вот этот не работает так как нужно(
Заранее благодарю за Ваш ответ, Маша
Мари, дело в том, что для возможности удаления строк и столбцов на защищенном листе необходимо, чтобы все ячейки в удаляемой строке или столбце имели атрибут "Не защищаемая". Я подробно рассматривал эти моменты в статье(видео прилагается): Защита листов и ячеек в MS Excel
Добрый день! А подскажите такую же команду только для Word. Здесь нет данной программы и функция не работает.
Вероника, в Word защита совершенно иначе работает и там просто нет аналогичной возможности.
Но как же тогда обеспечить работу макросов в защищенном режиме? Это вообще не возможно в word?
Вероника, во-первых непонятно, какую защиту Вы применяете в Word. От этого многое зависит. Во-вторых: можно кодом сначала снять защиту, потом поставить обратно. В Word также для документа есть методы Protect и UnProtect со своими параметрами. Их можно также просмотреть, записав макрорекордером установку защиты с нужными параметрами.
Добрый день! Дмитрий,вышеописанный Вами способ по защите от редактирования прекрасно работает. Но. Как быть если это все нужно реализовать в книге с общим доступом? Это возможно?
Обратите внимание на примечание в конце статьи. Там я четко написал, что при общем доступе данный метод использовать не получится.
Спрошу по-другому. Существует-ли способ добиться того же результата в книге с общим доступом? Или это в принципе невозможно?
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.
Во многих случаях вам может потребоваться защитить форматирование диапазона ячеек и разрешить ввод данных только в Excel. В этой статье представлены три метода для этого.
Защитите форматирование ячеек, но разрешите ввод данных только путем защиты рабочего листа
Чтобы защитить форматирование ячеек, но разрешить только ввод данных, вам необходимо сначала разблокировать эти ячейки, а затем защитить рабочий лист. Пожалуйста, сделайте следующее.
1. Выберите ячейки, которые необходимо защитить от форматирования, но разрешить только ввод данных, затем нажмите Ctrl + 1 клавиши одновременно, чтобы открыть Формат ячеек диалоговое окно.
2. в Формат ячеек диалоговом окне снимите флажок Заблокированный поле под Protection и нажмите OK кнопка. Смотрите скриншот:
3. Теперь нажмите Обзор > Защитить лист.
4. Укажите и подтвердите свой пароль в поле Защитить лист и Подтвердите пароль диалоговые окна. Смотрите скриншот:
Теперь лист защищен, и форматирование ячеек не изменилось. Но в указанные ячейки разрешен ввод данных только.
Защищать форматирование ячеек, но разрешать ввод данных только с кодом VBA
Вы также можете запустить следующий сценарий VBA, чтобы защитить форматирование ячеек, но разрешить ввод данных только в Excel. Пожалуйста, сделайте следующее.
1. Нажмите другой + F11 в то же время, чтобы открыть Microsoft Visual Basic для приложений окно.
2. в Microsoft Visual Basic для приложений окно, дважды щелкните ThisWorkbook на панели «Проект», а затем скопируйте и вставьте приведенный ниже код VBA в ThisWorkbook (Код) окно.
Код VBA: защитить форматирование ячеек, но разрешить только ввод данных
Внимание: В коде C2: C20 - это диапазон ячеек, в которые вы разрешаете ввод данных. Измените диапазон в соответствии с вашими потребностями.
3. нажмите F5 ключ для запуска кода. В появившемся диалоговом окне Kutools for Excel нажмите кнопку ОК.
Теперь рабочий лист защищен, как и форматирование ячеек. И только указанные ячейки разрешены для ввода данных.
Защитите форматирование ячеек, но разрешите ввод данных только с помощью Kutools for Excel
Вы можете легко разблокировать выбранные ячейки и защитить рабочий лист с помощью утилиты дизайна рабочего листа Kutools for Excel.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Нажмите Kutools Plus > Дизайн рабочего листа для активации Дизайн меню.
2. Выберите ячейки, которые необходимо защитить от форматирования, но разрешить только ввод данных, нажмите Разблокировать ячейки под Дизайн и нажмите OK кнопка во всплывающем окне Kutools for Excel диалоговое окно. Смотрите скриншот:
3. Нажмите Защитить лист под Дизайн меню.
4. Затем укажите и подтвердите свой пароль в Защитить лист и Подтвердите пароль диалоговые окна. Смотрите скриншот:
Теперь рабочий лист защищен. И в указанные ячейки разрешен ввод данных только.
Если вы хотите получить 30-дневную бесплатную пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Очень часто на своих тренингах и в форумах я слышу вопрос: как защитить доступ к книге так, чтобы для каждого пользователя был доступен только свой лист/листы? А другие ячейки или листы были недоступны для изменения или просмотра? Или скрыть отдельные столбцы с глаз пользователя? Часть подобного функционала предоставляется стандартными средствами Excel, а другая(например, доступность просмотра только конкретных листов) достигается только через макросы. В этой статье хочу привести несколько примеров реализации подобных разграничений прав между пользователями, их плюсы и минусы.
Что важно: не следует указывать здесь пароль, который совпадает хотя бы с одним из паролей для отдельных диапазонов. Думаю, понятно почему: чтобы защиту не могли снять те, кому этого не положено делать.
Теперь остается сообщить сотрудникам отделов их пароли: производственный - 2222 , коммерческий – 1111 .
При первой попытке изменить данные в ячейках C8:N11;C13:N14 - будет запрошен пароль на изменение ячеек созданного диапазона "коммерческий" ( 1111 ):
Если пользователю известен пароль для диапазона – его необходимо будет ввести лишь один раз. В дальнейшем для ввода данных в ячейки этого диапазона вводить пароль не придется до тех пор, пока файл не будет закрыт. После повторного открытия файла пароль необходимо будет указать заново.
Однако, если сотрудник другого отдела попытается изменить ячейки производственного отдела и пароль ему неизвестен – изменить данные этих ячеек не получится.
Также ни сотрудники коммерческого отдела, ни сотрудники производственного отдела не смогут изменить данные столбцов А и В(№ и наименование статьи), заголовки таблицы(строки с 1-ой по 7-ю) и строки с итоговыми формулами (12, 15 и т.д. – закрашенные зеленым). Они смогут изменять только те ячейки, которые перечислены в назначенных каждому отделу диапазонах. Внести данные в другие ячейки(не перечисленные в разрешенных диапазонах) можно будет исключительно сняв общий доступ с книги, а после этого защиту с листа –Рецензирование (Review) -группа Изменения (Changes) -Снять защиту листа (Unprotect sheet) . Но снять общую защиту сможет только тот, кто её создавал и кому известен "главный" пароль. Как правило это администратор или некий "смотрящий" файла и другие пользователи этот пароль не знают.
Плюс подобного метода в том, что такая защита может быть установлена для книги в общем доступе(подробнее про книги с общим доступом можно прочитать в статье - Ведение журнала сделанных в книге изменений).
Что необходимо учитывать для книг с общим доступом: создавать диапазоны для пользователей и устанавливать защиту на лист необходимо ДО назначения книге общего доступа, т.к. после того, как книге будет назначен общий доступ изменять параметры защиты листов и книги запрещено. При этом запрещены как установка защиты так и её снятие.
Минус данного метода в том, что нет дружественного интерфейса снятия защиты. Например, при попытке изменить какие-то ячейки одного из назначенных диапазонов нет никакой информации о том, что это за диапазон(коммерческий или производственный). Что в свою очередь может запутать пользователя. Так же данным методом невозможно скрыть листы, либо отдельные строки и столбцы. Можно лишь запретить изменение ячеек.
Разграничение прав доступа при помощи VBA
Самый большой минус всех методов ниже: они не будут работать при отключенных макросах (Что такое макрос и где его искать?). Во всех приложенных к статье файлах это предусмотрено и если макросы будут отключены, то пользователь увидит лист, предлагающий включить макросы. Подробнее см. в статье: Как запустить файл с включенными макросами?
Плюс подобного подхода - мы практически не ограничены в правилах: можем скрывать от отдельных пользователей любые листы, строки и столбцы, защищать отдельные ячейки и т.п. Я ниже приведу несколько вариантов реализации защиты кодами, а вам останется лишь выбрать тот, который больше подходит под задачу. Первые три больше демонстрационные, чтобы показать что можно сделать. А вот последний пример - Практический пример с использованием администратора - наиболее приближен к задачам, применяемым в работе и наиболее удобен для распространения среди пользователей.
Проект VBA во всех файлах открыт для просмотра и изменений . Однако перед распространением решений в реальности лучше его закрыть от просмотра и изменений - Как защитить проект VBA паролем.
Важно: приведенные ниже решения могут работать некорректно в книгах с общим доступом. А те решения, в которых устанавливается защита на листы вообще не будут работать, т.к. для книг с общим доступом невозможно изменять параметры защиты листов и книг.
В форме необходимо выбрать пользователя и указать пароль, соответствующий этому пользователю. Важно: Пароли и список доступных листов можно редактировать на очень скрытом листе "Users". Для каждого пользователя можно указать несколько листов. Указывать имена листов необходимо в точности такие же, какие они на самом деле. Это значит, что и регистр букв и каждый пробел должен быть учтен. Для разделения записей с несколькими листами используется точка-с-запятой(Лист1;Лист2;Лист3).
На листе "Main" перечислены имена пользователей, пароли для них и доступные для просмотра листы. Данная информация указаны только для ознакомления и тестов. Менять данные для реальных задач необходимо на листе "Users".
Важно: файл может работать нестабильно в книгах с общим доступом.
Читайте также: