Правильная защита макросом
В большинстве случаев защита макросом, которую я встречаю во множестве проектов, выглядит следующим образом:
Worksheets("Лист1").Unprotect Password:="123" 'тут макрос делает действия Worksheets("Лист1").Protect Password:="123"
То есть автор файла вынужден ставить защиту от шаловливых ручек пользователей, но чтобы его макрос мог выполнить необходимые действия, ему приходится временно снимать ее и затем включать снова. Такая техника работает, но далека от совершенства. Во-первых, это неудобно и требует введения подобных конструкций в каждый ваш макрос. Во-вторых, если выполнение макроса прервется с ошибкой, то лист останется незащищенным.
Есть гораздо более легкий и красивый способ решить задачу.
Нажмите Alt+F11, чтобы попасть в редактор Visual Basic. Затем найдите в левом верхнем углу в окне Project Explorer (если его не видно, то нажмите Ctrl+R) модуль ЭтаКнига (ThisWorkbook) и откройте двойным щелчком:
Скопируйте туда этот код:
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 разрешает пользоваться группировкой (символы плюс-минус для сворачивания-разворачивания строк и столбцов) на защищенном листе.
Всего три строчки кода, зато как удобно!
Ссылки по теме
Хочу поблагадарить создателей данного сайта. Благодаря ему началось мое посвящение в excel.
Предлагаемый вами вариант очень удобный, но имеет имеет и пробелы.
В защищенном листе отсутствует возможность фильтрации данных.
Ниже приведен макрос позволяющий ставить защиту на указанные листы и дает возможность пользователю использовать фильтр данных.
.EnableOutlining = True 'группировка строк/столбцов (символы структуры)
...и чтобы, если пользователь сознательно разблокировал лист, он автоматически блокировался при повторном открытии.
Private Sub Workbook_Open()
'включаем защиту первого листа для пользователя, но не макроса
Worksheets("Лист1").Protect Password:="123", UserInterfaceOnly:=True ?????????
End Sub
Worksheets("Лист2".EnableOutlining = True
Worksheets("Лист2".Protect Password:="555", UserInterfaceOnly:=True, AllowFiltering:=True, , AllowSorting:=True
Хочу спросить... здесь не так много уделено внимания защите от изменения, что удивительно, поскольку я сталкиваюсь все время с тем, что незащищенные программки очень быстро ломают любопытствующие. Поэтому хочу спросить есть ли еще какие нибудь приемы/советы по защите. А второе: какую лучше процедуру в VBA прописать и на какое событие, проверяющее листы на то, защищены ли они еще паролем. Т.к. пароли легко взламываются и защита с листов снимается. Спасибо
Заранее спасибо
В Excel нормальной защиты нет
Обход действия макроса я думал решить тем, что листы с данными остаются в veryhidden и открываться будут только по запуску макросов, т.е. без макросов сам файл не будет иметь смысла, он ничего считать не будет.
Ну да, если человек знает как обойти действие макросов, от него уже спасу нет
Большое спасибо за ответы
Скройте все листы кроме одного с помощью xlVeryHidden, поставьте защиту книги, поставьте защиту кода VBA. Оставьте одну кнопку, которая будет запускать макрос для разблокировки листов и делать то, что вам нужно.
Подскажите ,не сочтите за труд , как обстоят дела с защитой VBA проекта ,когда вы сохраняете его как надстройку? Это интересная тема поскольку многие стремятся ограничить проникновение в исходный код а сама защита VBA проекта снимается специализированным софтом.Если есть дельный совет,поделитесь)8)
как получить доступ к исходнику?
вот код
Я немного неверно описал проблему - при нажатии на фильтр в ячейке появляется возможность отсортировать ячейки - не работает именно сортировка ячеек, а сам фильтр работает нормально. Прошу прощенья за неточность в вопросе!
Реализуема ли средствами excel такая задача: есть несколько ячеек в строке куда вводятся данные "пользователями" и последняя ячейка в строке которую заполняет "руководителем". После того как руководитель ввел сою цифру остальные ячейки в строке должны стать недоступными для редактирования?
Как-то так.
Новый файл остается незащищенным, как его после копирования и сохранения защитить не заметно для пользователя.
Подскажите, пожалуйста, что необходимо дописать в макрос, чтобы защищенные ячейки невозможно было выделить?
такая защита не прокатит
Реализуема ли средствами excel такая задача: есть несколько ячеек в строке куда вводятся данные "пользователями" и последняя ячейка в строке которую заполняет "руководителем". После того как руководитель ввел сою цифру остальные ячейки в строке должны стать недоступными для редактирования?
Подскажите пожалуйста как установить подобную защиту не только на Лист1 и Лист2 , а на всю книгу ?? Заранее спасибо
....
Range("ИТОГО").Copy
Range("наличие").PasteSpecial Paste:=xlPasteValues
...
вызывает ошибку "Метод класса Range PasteSpecial завершен неверно"
В файле Excel2007 с Листами1 и 2, проделал операцию от попадания в редактор Visual Basic до вставки предложенного Вами кода, закрыл окно Visual Basic. Однако ничего не изменилось, защита листов не установилась, при попытке закрыть файл возникла необходимость в сохранении его как книга Excel с поддержкой макросов. Но, и вновь, после открытия файла ничего не изменилось. При вводе комбинации Alt+F11 код никуда не исчез. Подскажите, что не так? Очень нужна для работы такая функция. Заранее спасибо!!!
Спасибо за Ваш сайт, давно уже пользуюсь, узнал много нового.
По освещенной теме возникло два вопроса.
1. Раньше я защищал макросом лист именно так, как описано в начале статьи. Есть таблица сотрудников, заполняется форма и макросом копируется строка значений в таблицу (по алгоритму снять защиту-скопировать-вставить значения-поставить защиту). Почему-то макрос работает четко через раз. Один раз проходит, второй раз выдает ошибку при вставке значений. Если убрать Protect - Unprotect, то все работает. При этом если проверить лист на защищенность, то защита снята. Что бы это могло быть?
2. В связи с вышесказанным пришлось искать другой вариант защиты листа, нашел метод из этой статьи. Сразу не заработало, т.к. я по привычке переименовал макрос по своему.
Правильно ли я понимаю, что название должно быть жестко Workbook_Open и переименовывать его нельзя?
Заранее Спасибо.
Владимир
Сняла защиту с ячеек, которые сотрудник может редактировать/вносить данные.
Какой макрос следует написать, если в незащищенной ячейке (выпадающий список), сотрудник может выбрать значение, которое влияет на защищенную ячейку?
Я совсем начинающий пользователь... надеюсь на помощь.
Но у меня есть один нюанс. Как разрешить добавлять и удалять строки?
AllowInsertingRows:=True, AllowDeletingRows:=True - не помогает, ругается "эта ячейка находится на защищённом листе"
Я далек от этой темы, и буду очень благодарен если мне поможете.
Чтобы не снимать защиту перед каждым действием и не ставить ее потом обратно и используется параметр UserInterfaceOnly.
Вопрос интересный - стоит задача - Чтобы сотрудники не вносили/редактировали данные задним числом - сделать так чтобы АВТОМАТИЧЕСКИ включалась БЛОКИРОВКА редактирования записей которые внесли вчера на СЛЕДУЮЩИЙ ДЕНЬ!! и можно вносить новые записи - Но всё таки была возможность у админа снять блокировку прошлых записей и отредактировать при необходимости.
видел такой способ для одной ячейки - вручную
— Поставьте курсор на ячейку с датой и выберите в меню пункт «Данные».
— Нажмите на кнопку «Проверка данных». Появится таблица.
— В выпадающем списке «Тип данных» выбираем «Другой».
— В графе «Формула» пишем =А2=СЕГОДНЯ()
— Убираем галочку с «Игнорировать пустые ячейки» (картинка 2).
— Нажимаем кнопку «ОК». Теперь, если человек захочет ввести другую дату, появится предупреждающая надпись (картинка 3).
— Также можно запретить изменять цифры в столбце «Кол-во». Ставим курсор на ячейку с количеством и повторяем алгоритм действий.
может он сработает для всей колонки ДАТА и Количество?
И остаётся вопрос пароля на установку блокировки?
Подскажите реально ли без надстроек скрывать лист2 до заполнения листа1?
Например пока не будет заполнена конкретная ячейка на листе1, листа2 вообще не будет видно в книге.
У меня в файле много вкладок, которые надо блокировать, при передаче пользователям.
И так как блокировать каждый лист долго, я придумала макросы по блокировке и разблокировке всех листов.
Вот такие
Sub blok()
Worksheets("ЗП_и_пр";.Protect Password:="ufcytncdtn"
...
End Sub
Sub razblok()
Worksheets("ЗП_и_пр";.Unprotect Password:="ufcytncdtn"
...
End Sub
Но вот проблема, при таком методе высоту строк пользователь не может регулировать.
Думаю есть методы для изменения высоты строк пользователем при заблокированном листе.
Кто может - помогите)))
Заранее, спасибо.
, после защиты макросом, я пытаюсь на др листах прописать текст в ячейках, соответственно на первом листе вновь вводимые данные должны отобразиться, вместо этого - возникает сл ошибка:
в Первом листе "сводном" с именем "Заявки" у меня записан вот такой код:
взятый код с вашего сайта и записанный в модуль ЭтаКнига (ThisWorkbook)
не работает с книгой UserInterfaceOnly:=True,
Реализовал защиту листов. Все работает норм. При открытии книги все листы блокируются через макрос с параметром UserInterfaceOnly:=True. Все отлично. Данный параметр не дает на листе работать пользователю, но макросы работают нормально, например логирование сохранений файла работает.
Проблема заключается в следующем. При попытке собрать данные со всех листов на другом вылазит ошибка, что данные находятся на защищенном листе, и его изменение не возможно. Если защиту снимаю, то макрос отрабатывает нормально. Может это из-за того, что данные на листе организованы через умные таблицы?
Private Sub Workbook_Open()
Sheets("янв").EnableOutlining = True
Sheets("янв").Protect Password:="01081985", UserInterfaceOnly:=True
Sheets("фев").EnableOutlining = True
Sheets("фев").Protect Password:="01081985", UserInterfaceOnly:=True
Sheets("мар").EnableOutlining = True
Sheets("мар").Protect Password:="01081985", UserInterfaceOnly:=True
Sheets("апр").EnableOutlining = True
Sheets("апр").Protect Password:="01081985", UserInterfaceOnly:=True
Sheets("май").EnableOutlining = True
Sheets("май").Protect Password:="01081985", UserInterfaceOnly:=True
Sheets("июн").EnableOutlining = True
Sheets("июн").Protect Password:="01081985", UserInterfaceOnly:=True
Sheets("июл").EnableOutlining = True
Sheets("июл").Protect Password:="01081985", UserInterfaceOnly:=True
Sheets("авг").EnableOutlining = True
Sheets("авг").Protect Password:="01081985", UserInterfaceOnly:=True
Sheets("сен").EnableOutlining = True
Sheets("сен").Protect Password:="01081985", UserInterfaceOnly:=True
Sheets("окт").EnableOutlining = True
Sheets("окт").Protect Password:="01081985", UserInterfaceOnly:=True
Sheets("ноя").EnableOutlining = True
Sheets("ноя").Protect Password:="01081985", UserInterfaceOnly:=True
Sheets("дек").EnableOutlining = True
Sheets("дек").Protect Password:="01081985", UserInterfaceOnly:=True
End Sub
Для установки "примечаний" установила галочку на "изменение объектов" в защите листа, но после открытия заново файла примечание не работает(((, а с группировкой все ок. Форматирование, фильтрация и сортировка не нужна. Помогите плиз
Sheets("янв").EnableOutlining = True
Sheets("янв").Protect Password:="01081985", UserInterfaceOnly:=True, DrawingObjects:=False, Contents:=True
End Sub
Попробуте так.
у меня к сожалению при использовании данной защиты не работает макрос для автоматической установки ширины столбца:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.EntireColumn.AutoFit
End Sub
Выдает ошибку:
Run-time error 1004
AutoFit method of Range class failed.
Посоветуйте пожалуйста как быть.
Спасибо!
Всё классно, пока файл не закрыт.
Я защитил каждый лист файла с возможностью редакции макросом (Не через "эта книга").
Полагал, что при закрытии файла это свойство сохранится.
Однако, после открытия файла заново, защита сохранилась, но макроса не смог внести изменения в защищённый лист.
Подскажите пожалуйста как это исправить?
Спасибо
Я перепробовал разные комбинации аргументов, но так и не понял, как разрешить добавление данных макросом в data table на листе. Кто-нибудь сталкивался?
И еще. Получается если в настройках отключить макросы то все эти заморочки становятся бесполезны?