Правильная защита макросом

111556 21.10.2012 Скачать пример

В большинстве случаев защита макросом, которую я встречаю во множестве проектов, выглядит следующим образом:

Worksheets("Лист1").Unprotect Password:="123"
'тут макрос делает действия
Worksheets("Лист1").Protect Password:="123"

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

Есть гораздо более легкий и красивый способ решить задачу.

Нажмите Alt+F11, чтобы попасть в редактор Visual Basic. Затем найдите в левом верхнем углу в окне Project Explorer (если его не видно, то нажмите Ctrl+R) модуль ЭтаКнига (ThisWorkbook) и откройте двойным щелчком:

smart_protection1.gif

 

Скопируйте туда этот код:

Private Sub Workbook_Open()
    'включаем защиту первого листа для пользователя, но не макроса
    Worksheets("Лист1").Protect Password:="123", UserInterfaceOnly:=True
    
    'второй лист защищаем аналогично, но с возможностью пользоваться группировкой
    Worksheets("Лист2").EnableOutlining = True
    Worksheets("Лист2").Protect Password:="555", UserInterfaceOnly:=True
End Sub

Эта процедура будет автоматически запускаться при открытии файла и ставить защиту на заданные листы книги. Причем параметр UserInterfaceOnly, который мы дополнительно ввели, указывает Excel, что защита не должна распространяться на действия выполняемые макросом, а только на операции пользователя. Для второго листа все еще веселее - строка с параметром EnableOutlining разрешает пользоваться группировкой (символы плюс-минус для сворачивания-разворачивания строк и столбцов) на защищенном листе.

Всего три строчки кода, зато как удобно!

Ссылки по теме

 



Александр
21.10.2012 13:29:05
Красиво! В мемориз!
Дамир Шарафеев
21.10.2012 13:30:04
Доброго времени суток всем.
Хочу поблагадарить создателей данного сайта. Благодаря ему началось мое посвящение в excel.
Предлагаемый вами вариант очень удобный, но имеет имеет и пробелы.
В защищенном листе отсутствует возможность фильтрации данных.
Ниже приведен макрос позволяющий ставить защиту на указанные листы и дает возможность пользователю использовать фильтр данных.
Private Sub Workbook_Open()
 Const MyPassword = "1111" ' Указываем пароль на листы
 With Sheets("Tashkent") 'Указываем наименование листа
 .Unprotect Password:=MyPassword
 .EnableOutlining = True
 .Protect Password:=MyPassword, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
 AllowFiltering:=True, UserInterfaceOnly:=True
 End With
 With Sheets(" Tashkent Oblast")
 .Unprotect Password:=MyPassword
 .EnableOutlining = True
 .Protect Password:=MyPassword, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
 AllowFiltering:=True, UserInterfaceOnly:=True
 End With
 End Sub
09.05.2016 15:47:26
Добрый день. Спасибо, код работает отлично. Но я не очень понимаю, зачем использовать макрос, когда то же самое можно сделать с помощью "Рецензирование - Защитить лист".  Буду очень признателен, Если кто нибудь пояснит.
Вот из-за этой строчки:
.EnableOutlining = True  'группировка строк/столбцов (символы структуры)

...и чтобы, если пользователь сознательно разблокировал лист, он автоматически блокировался при повторном открытии.
Максим
21.10.2012 13:31:01
Добрый день. Подскажите пожалуйста, как защитить таким методом не весь лист, а только определенные ячейки на листе (некоторые столбцы) ?
21.10.2012 13:32:20
Нужно заранее снять галочку в окне Формат ячейки - вкладка Защита - Защищаемая ячейка для тех столбцов, которые должны остаться доступными после включения защиты.
12.02.2013 15:38:26
Добрый день! Спасибо, огромное! Николай, подскажите, пожалуйста, если к разрешению группировки нужно добавить еще возможность сортировки и использования автофильтра, что в этот макрос требуется добавить? Вернее, как это должно выглядеть? Спасибо.
13.02.2013 16:26:51
Выглядеть должно так
Worksheets("Лист2").EnableOutlining = True 
Worksheets("Лист2").Protect Password:="555", UserInterfaceOnly:=True, AllowFiltering:=True, , AllowSorting:=True
14.02.2013 15:21:29
Спасибо! Очень помогли!
23.02.2015 15:56:35
Добрый день, не могли бы мне помочь, т.к. я не очень разбираюсь в модулях. Мне к разрешению автоматического запуска при открытии файла и включения защиты на заданные листы книги, нужно добавить только возможность использования (включения) функции изменения диапозонов, что в этот макрос требуется добавить??? Вернее, как это должно выглядеть? Спасибо.


Private Sub Workbook_Open()
   'включаем защиту первого листа для пользователя, но не макроса
   Worksheets("Лист1").Protect Password:="123", UserInterfaceOnly:=True ?????????

End Sub
17.04.2015 12:24:30
Добрый день, подскажите пожалуйста куда в макросе это нужно вставить? Что бы работал автофильтр? Спасибо.

Worksheets("Лист2".EnableOutlining = True
Worksheets("Лист2".Protect Password:="555", UserInterfaceOnly:=True, AllowFiltering:=True, , AllowSorting:=True
15.02.2013 02:59:33
Пользуясь случаем, хочу выразить свое восхищение автору сайта! Мало того, что я нашел здесь несколько приемов над которыми ломал голову уже несколько месяцев, так они еще и настолько просто и изящно написаны, что остается только удивляться.  

Хочу спросить... здесь не так много уделено внимания защите от изменения, что удивительно, поскольку я сталкиваюсь все время с тем, что незащищенные программки очень быстро ломают любопытствующие. Поэтому хочу спросить есть ли еще какие нибудь приемы/советы по защите. А второе: какую лучше процедуру в VBA прописать и на какое событие, проверяющее листы на то, защищены ли они еще паролем. Т.к. пароли легко взламываются и защита с листов снимается. Спасибо
18.02.2013 12:20:54
Самое просто - это попытаться изменить какую-либо ячейку на листе. Если позволит - лист защищен, если выдаст ошибку - защита есть. Посмотрите в справке работу с объектом Err - в нем содержится информация об ошибках выполнения макроса.
21.02.2013 14:08:01
Я, скорее всего неточно сформулировал вопрос... или неправильно понимаю ответ, тогда заранее прошу извинить. Вопрос: допустим, я защитил свой файл от изменений в тех местах, где ничего не должно меняться и оставил доступ только к вводу данных там, где менять можно. Допустим кто-то из любопытства, взломал пароль и снял защиту и, само собой, давай менять те данные, к которым им доступа не было . Можно ли прописать процедуру, проверяющую не снята ли защита и ... к примеру ставящая эту защиту сразу обратно и на какое событие его можно поставить выполняться? Я пробовал в прошлом на пересчет листа, но она не выполняется. На открытие нового листа не очень подходит, т.к. иногда данные вводятся на одном листе.
Заранее спасибо
21.02.2013 22:34:43
Бессмысленная затея, Денис. Тот, кто сумеет взломать вашу защиту легко обойдет такой макрос для ее восстановления. Например откроет ваш файл, а макросы не разрешит. Или пересохранит файл в формате xlsx, а не xlsm - и все макросы умрут.
В Excel нормальной защиты нет :(
01.03.2013 17:49:01
Ясно, спасибо :)

Обход действия макроса я думал решить тем, что листы с данными остаются в veryhidden и открываться будут только по запуску макросов, т.е. без макросов сам файл не будет иметь смысла, он ничего считать не будет.
Ну да, если человек знает как обойти действие макросов, от него уже спасу нет :). Но вот такую защиту "от дурака"... Что нибудь можно придумать стоящее? Просто хуже всего, что ломают программу именно люди, которые не знают толком, ни что такое макросы, ни как что работает. А защиту взламывают с помощью сторонних программ, т.е. особо не разбираясь в Excel.
Большое спасибо за ответы
09.03.2013 08:21:52
От профессионала защиты нет, но ему можно осложнить жизнь :)
Скройте все листы кроме одного с помощью xlVeryHidden, поставьте защиту книги, поставьте защиту кода VBA. Оставьте одну кнопку, которая будет запускать макрос для разблокировки листов и делать то, что вам нужно.
29.05.2015 17:56:27
Здраствуйте, а можно по подробней? Вот пример: есть 31 лист, как сделать чтобы люди авторизовывались и у всех были свои права, у 1 редактирование, у 2 только ввод данных (в таблице определенные ячейки не будут защищенны для этого, я так понял), и у 3 только просмотр и печать.
18.02.2013 11:12:10
Уважаемые знатоки, пример защиты приведенный Дамиром Шарафеевым от 21.10.2012 13:30:04 работает корректно в монопольном режиме, но если поставить галочку совместного использования файла ("Защитить книгу и дать общий доступ"), макрос выдает ошибку "Runtime error 1004. Method "Unprotect" of object "_Worksheet" failed/". Пытаясь найти решения наткнулся на ограничения режима общего доступа http://bbs.vbstreets.ru/viewtopic.php?p=6634774#6633343.  Вопрос, может кто сталкивался с этим и знает как это можно обойти? Задача: защитить лист, от пользователя а не от макроса, при общем доступе к файлу? Буду очень признателен. Версия 2010.
18.02.2013 12:22:50
В общем доступе защита не работает. Ни в какой версии.
Прочитал данную тему, но не совсем понятно есть ли какая нибудь защита макроса от дурака, просто файл находиться в общем доступе и любой может зайти в "исходный текст" и испортить макрос. Заранее благодарен.
18.04.2013 00:11:32
В редакторе VBA в меню Tools - VBAProject Properties - вкладка Protection - галочка Lock project for viewing и задать пароль.
30.06.2013 17:28:16
Николай добрый день!
Подскажите ,не сочтите за труд , как обстоят дела с защитой VBA проекта ,когда вы сохраняете его как надстройку? Это интересная тема поскольку многие стремятся ограничить проникновение в исходный код а сама защита VBA проекта снимается специализированным софтом.Если есть дельный совет,поделитесь)8)
01.07.2013 20:56:05
Много раз говорил и повторю еще раз: "Нормальной защиты в Excel нет!". Единственный более-менее трудновзламываемый вариант - это выносить код функций и процедур в отдельную скомпилированную DLL-библиотеку, которую подключать в макросе.
14.08.2013 10:42:50
У меня в данном случае выдает "Project is unviewable"
как получить доступ к исходнику?
14.04.2013 23:00:06
Форматирование на защищенном листе работает, а вот фильтрация нет! Подскажите в чем может быть проблема?
вот код

.Protect Password:=MyPassword, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
 AllowFiltering:=True,  AllowSorting:=True, UserInterfaceOnly:=True
при попытке фильтрации говорит что ячейки защищены
18.04.2013 00:07:50
Вы автофильтр заранее включили или пытаетесь его включить на защищенном листе? Команда AllowFiltering:=True означает, что вы можете использовать заранее включенный автофильтр, а не включать-выключать его.
18.04.2013 14:33:38
Фильтрация задается заранее.
Я немного неверно описал проблему - при нажатии на фильтр в ячейке появляется возможность отсортировать ячейки - не работает именно сортировка ячеек, а сам фильтр работает нормально. Прошу прощенья за неточность в вопросе!

AllowSorting:=True
Сортировку включаю, вроде.
19.04.2013 22:01:46
Команда AllowSorting:=true разрешает сам факт сортировки (с помощью автофильтра). Но для того, чтобы ее выполнить (т.е. переставить строки местами) все ячейки сортируемой таблицы (включая шапку) не должны быть защищены.
04.05.2013 17:58:05
Николай, скажите можно ли пароль в макросе сделать не статичным(зашитым в теле макроса), а вводимым пользователем ? Каким образом будет выглядеть макрос в этом случае?
17.05.2013 23:40:42
Можно выводить диалоговое окно с запросом пароля. Например, так:
pass=inputbox("Введите пароль")
Worksheets("Лист1").Protect Password:=pass
19.05.2013 08:40:42
Николай, Спасибо большое!
24.05.2013 20:52:25
Николай!
Реализуема ли средствами excel такая задача: есть несколько ячеек в строке куда вводятся данные "пользователями" и последняя ячейка в строке которую заполняет "руководителем". После того как руководитель ввел сою цифру остальные ячейки в строке должны стать недоступными для редактирования?
26.05.2013 09:40:36
Да, конечно. Но в двух словах не ответить - достаточно сложная штука будет. Нужно на входе в файл запрашивать имя и пароль, чтобы понять - кто его открывает, пользователь или руководитель? А потом макросом снимать защиту или полностью (для руководителя) или выборочно (для пользователей).

Как-то так.
25.06.2013 15:15:05
Здравствуйте, подскажите и мне. Я защитила шаблон этим способом "Tools - VBAProject Properties - вкладка Protection - галочка Lock project for viewing и задать пароль". Шаблон редактируется, макросом копируются листы и пересохраняются с другим именем, на скопированных листах есть макросы, шаблон закрывается.
Новый файл остается незащищенным, как его  после копирования и сохранения защитить не заметно для пользователя.
01.07.2013 21:12:43
Никак. Защита VBA проекта программно не ставится.
12.09.2013 21:46:59
Спасибо большое автору за полезный макрос. Очень пригодился!

Подскажите, пожалуйста, что необходимо дописать в макрос, чтобы защищенные ячейки невозможно было выделить?
28.11.2013 10:15:13
самое интересное если открывать такой файл в программе  OpenOffice то весь макрос как на ладони
такая защита не прокатит
24.02.2014 11:34:00
Николай!
Реализуема ли средствами excel такая задача: есть несколько ячеек в строке куда вводятся данные "пользователями" и последняя ячейка в строке которую заполняет "руководителем". После того как руководитель ввел сою цифру остальные ячейки в строке должны стать недоступными для редактирования?
Доброго времени суток. У меня подобная задача, только надо просто запретить изменение ячейки после заполнения всем кроме "Ответственного". Спасибо.
24.04.2014 07:31:45
Да, полезная штучка! Спасибо большое !
16.06.2014 16:12:12
У UserInterfaceOnly:=True есть одно мерзкое свойство - оно действует только на время текущей сессии работы с файлом. То есть не получится защитить лист с этим параметром не в событии Workbook_Open, а как-то иначе. В противном случае макрос не получит доступа к этому листу после переоткрытия файла. :)
10.10.2014 20:49:56
Николай здравствуйте!!!
Подскажите пожалуйста как установить подобную защиту не только на Лист1 и Лист2 , а на всю книгу ?? Заранее спасибо
30.05.2015 09:14:02
При открытии книги нужно в цикле перебрать листы и установить защиту на каждый - иначе никак. В коде это будет:
For Each ws in ActiveWorkbook.Worksheets
   ws.Protect Password:="123", UserInterfaceOnly:=True
Next ws
04.02.2015 16:20:55
У предложенного метода правильной защиты листа от пользователя есть один нюанс - на защищенном таким способом листе код :
....
Range("ИТОГО").Copy
Range("наличие").PasteSpecial Paste:=xlPasteValues
...
вызывает ошибку "Метод класса Range PasteSpecial завершен неверно"
07.09.2015 23:31:05
Николай здравствуйте!!!
В файле Excel2007 с Листами1 и 2, проделал операцию от попадания в редактор Visual Basic до вставки предложенного Вами кода, закрыл окно Visual Basic. Однако ничего не изменилось, защита листов не установилась, при попытке закрыть файл возникла необходимость в сохранении его как книга Excel с поддержкой макросов. Но, и вновь, после открытия файла ничего не изменилось. При вводе комбинации Alt+F11 код никуда не исчез. Подскажите, что не так? Очень нужна для работы такая функция. Заранее спасибо!!!
27.09.2015 17:01:27
Здравствуйте Николай.
Спасибо за Ваш сайт, давно уже пользуюсь, узнал много нового.
По освещенной теме возникло два вопроса.

1. Раньше я защищал макросом лист именно так, как описано в начале статьи. Есть таблица сотрудников, заполняется форма  и макросом копируется строка значений в таблицу (по алгоритму снять защиту-скопировать-вставить значения-поставить защиту). Почему-то макрос работает четко через раз. Один раз проходит, второй раз выдает ошибку при вставке значений. Если убрать Protect - Unprotect, то все работает. При этом если проверить лист на защищенность, то защита снята. Что бы это могло быть?

2. В связи с вышесказанным пришлось искать другой вариант защиты листа, нашел метод из этой статьи. Сразу не заработало, т.к. я по привычке переименовал макрос по своему.
Правильно ли я понимаю, что название должно быть жестко Workbook_Open и переименовывать его нельзя?

Заранее Спасибо.
Владимир
22.12.2017 15:09:31
Этот код вставляется в модуль Книги, а название макроса "Workbook_Open", означает что заработать он должен при событии "Открытия книги". Поэтому название макроса не может быть другим.
30.09.2015 19:04:04
Добрый день!
Сняла защиту с ячеек, которые сотрудник может редактировать/вносить данные.
Какой макрос следует написать, если в незащищенной ячейке (выпадающий список), сотрудник может выбрать значение, которое влияет на защищенную ячейку?
Я совсем начинающий пользователь... надеюсь на помощь.
22.11.2015 09:12:11
Ещё один косяк у UserInterfaceOnly:=True состоит в том, что не получится манипулировать в макросе строками умной таблицы через родные объекты ListObject. Вообще умные таблицы и защита листа Excel - это 2 антипода, друг друга на дух не переносящие :)
10.03.2016 14:41:58
Отличное решение, спасибо автору!
Но у меня есть один нюанс. Как разрешить добавлять и удалять строки?
AllowInsertingRows:=True, AllowDeletingRows:=True  - не помогает, ругается "эта ячейка находится на защищённом листе"

Я далек от этой темы, и буду очень благодарен если мне поможете.
Николай, добрый день! Подскажите как дополнить ваш макрос чтоб блокировать не весь лист, а только некоторые диапазоны ячеек различные на каждом листе. А также защитить форматы таблиц (ширина, высота, стиль, цвет), в том числе не защищенных ячеек. Спасибо.
09.05.2016 15:48:41
Добрый день. Я не очень понимаю, зачем использовать макрос, когда то же самое можно сделать с помощью "Рецензирование - Защитить лист". Буду очень признателен, Если кто нибудь пояснит.
10.05.2016 09:46:51
Фаррух, речь о защите, когда вы используете макросы на защищенном листе.
Чтобы не снимать защиту перед каждым действием и не ставить ее потом обратно и используется параметр UserInterfaceOnly.
10.05.2016 20:09:36
Николай, спасибо большое.
21.05.2016 17:40:53
День Добрый  :)
Вопрос интересный - стоит задача - Чтобы сотрудники не вносили/редактировали данные задним числом - сделать так чтобы АВТОМАТИЧЕСКИ включалась БЛОКИРОВКА  редактирования записей которые внесли вчера на СЛЕДУЮЩИЙ ДЕНЬ!! и можно вносить новые записи -  Но всё таки была возможность у админа снять блокировку прошлых записей и отредактировать при необходимости.

видел такой способ для одной ячейки - вручную
— Поставьте курсор на ячейку с датой и выберите в меню пункт «Данные».
— Нажмите на кнопку «Проверка данных». Появится таблица.
— В выпадающем списке «Тип данных» выбираем «Другой».
— В графе «Формула» пишем =А2=СЕГОДНЯ()
— Убираем галочку с «Игнорировать пустые ячейки» (картинка 2).
— Нажимаем кнопку «ОК». Теперь, если человек захочет ввести другую дату, появится предупреждающая надпись (картинка 3).
— Также можно запретить изменять цифры в столбце «Кол-во». Ставим курсор на ячейку с количеством и повторяем алгоритм действий.

может он сработает для всей колонки ДАТА и Количество?
И остаётся вопрос пароля на установку блокировки?
16.07.2016 08:53:50
Отличный сайт, все что нужно было найти для работы - нашел здесь!!!
30.11.2016 05:43:08
Доброго дня!
Подскажите реально ли без надстроек скрывать лист2 до заполнения листа1?
Например пока не будет заполнена конкретная ячейка на листе1, листа2 вообще не будет видно в книге.
20.12.2016 20:13:41
Добрый день! Подскажите пожалуйста,  после  защиты макросом на работает счётчик (элемент управления формы), как устранить эту проблему?
15.03.2017 11:55:10
Здравствуйте, друзья.

У меня в файле много вкладок, которые надо блокировать, при передаче пользователям.
И так как блокировать каждый лист долго, я придумала макросы по блокировке и разблокировке всех листов.
Вот такие

Sub blok()
Worksheets("ЗП_и_пр";.Protect Password:="ufcytncdtn"
...
End Sub

Sub razblok()
Worksheets("ЗП_и_пр";.Unprotect Password:="ufcytncdtn"
...
End Sub

Но вот проблема, при таком методе высоту строк пользователь не может регулировать.

Думаю есть методы для изменения высоты строк пользователем при заблокированном листе.

Кто может - помогите)))

Заранее, спасибо.
17.08.2017 11:01:35
Добрый День! Подскажите как обойти ошибку? За ранее спасибо!

, после защиты макросом,  я пытаюсь на др листах прописать текст в ячейках, соответственно на первом листе вновь вводимые данные должны отобразиться, вместо этого - возникает сл ошибка:





в Первом листе "сводном" с именем "Заявки" у меня записан вот такой код:
Option Explicit
Const rrow = 1

Private Sub Worksheet_Activate()
    Dim r As Range, sh As Worksheet, ind&
    Application.ScreenUpdating = False

    Range("a" & rrow & ":n" & Cells(rrow, 2).End(xlDown).Row).Clear
    For Each sh In Worksheets
        With sh
            If .Index <> ActiveSheet.Index Then
                Set r = .Range("a" & rrow & ":n" & .Cells(.Rows.Count, 1).End(xlUp).Row)
                r.Copy Cells(rrow + ind, 1)
                ind = ind + r.Rows.Count
            End If
        End With
    Next

    Application.ScreenUpdating = True

End Sub

взятый код с вашего сайта и записанный в модуль ЭтаКнига (ThisWorkbook)

Private Sub Workbook_Open()
  Worksheets("Заявки";) .Protect Password:="123", UserInterfaceOnly:=True
End Sub
13.09.2017 23:40:28
Можно ли защитить не листы, а книгу чтоб потом макросом можно было скрывать листы?

Workbooks("тест.xlsb").Protect Password:="1", UserInterfaceOnly:=True, Structure:=True, Windows:=False


не работает с книгой UserInterfaceOnly:=True,
15.12.2017 09:03:08
Подскажите, пожалуйста, как защитить лист, чтобы можно было пользоваться только срезами сводной таблицы без возможности их удаления? Срезы работают только если разрешить "Использовать сводную таблицу и диаграмму" и "изменение объектов". При этом пункт "изменение объектов" позволяет выделить срез и удалить его. А также любой другой объект на листе.
19.01.2018 11:10:59
Добрый день! Можно ли защитить книгу так, чтобы на любом листе данные вставлялись только как значения?
30.01.2018 01:06:01
Доброе время всем. Подскажите пожалуйста. Мне надо  на защищенном ВАШИМ кодом листе помимо группировки еще вручную скрывать столбцы. Рецензирование - защитить лист - ставлю галочку форматирование столбцов, пароль, еще раз пароль. Все работает отлично. Сохраняю, закрываю. Снова открываю а отобразить или скрыть столбцы  вручную - немогу (неативно). Надо снова снимать защиту. В чет проблема ??? И еще вопрос но уже не совсем в тему: как макросом на защищенном Вашим кодом листе скрыть столбцы из определенного диапазона  при определенном условии (например от столбца S до Y, скрыть столбцы в которых результат вычислений формул в строке 3 = 0
15.11.2018 17:12:09
спасибо, просто и элегантно!
Наверх