Снять защиту с ячейки excel vba

Обновлено: 04.07.2024

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

В этой статье мы разберем как защитить лист Excel от изменений и редактирования, а также как снять защиту.

Основы защиты данных в Excel

Защита листов в Excel не позволяет надежно ограничить доступ к данным в ваших файлах или листах. Связано это с тем, что в Excel применяется простой алгоритм кодирования пароля, который легко взломать. В старых версиях Excel (ранние версии, ниже чем Excel 2010) используется на столько простой алгоритм кодирования, что любой человек, разбирающийся в VBA может его взломать. В версиях Excel 2013 и 2016 используется более усовершенствованный алгоритм шифровки, но и к нему есть не сложные подходы для взлома.

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

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

Как защитить лист Excel

Для защиты листа в Excel проделаем следующие шаги:

Как снять защиту с листа Excel зная пароль

Если вы знаете пароль к листу то, снять защиту не составит труда.

Как снять защиту листа в Excel если забыл пароль

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

Снять защиту листа Excel без пароля с помощью VBA кода (для Excel 2010 и ниже)

Если вы используйте версию Excel 2010 и ниже, то вы можете снять защиту листа без пароля с помощью макроса. Если вы используете версию Excel 2013 или 2016, то сохраните защищенный файл в формате xls (Книга Excel 97-2003). Затем проделайте следующие действия:

Снять защиту с листа Excel с помощью копирования данных на другой лист

Еще одним способом снятия защиты с листа Excel является копирование содержимого защищенного листа на новый лист Excel. Для этого проделайте следующие действия:

Откройте файл Excel с защищенным листом;
Перейдите на защищенный лист и выделите левой клавишей мыши ячейку А1 ;
Нажмите сочетание клавиш Shift + Ctrl + End для выделения всего содержимого на листе;
Создайте новый лист в рабочей книге Excel ( Ctrl + N );
Левой клавишей мыши нажмите на ячейку А1 на новом листе;
Нажмите сочетание клавиш Ctrl + V и вставьте скопированные данные.

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

Некоторое время назад меня попросили «помочь с Экселем», а потом и работа подвернулась такая, так что за последние пару месяцев я узнал много полезного, чем и хочу поделиться в догонку к недавней статье.

Предполагается, что вы знаете основы Visual Basic. Я не буду рассказывать, как создавать формы или модули, здесь только примеры кода.

Visual Basic

Опции

Во-первых, в VB массивы могут начинаться с индекса 1, что для многих странно, поэтому в начале модулей можно прописывать:

Так же рекомендуется прописать:

В этом случае интерпретатор потребует заблаговременного объявления всех переменных. Переменные объявлять нужно потому, что:
— VB запомнит их нАпиСание и не будет исправлять во всём коде на последний введенный вариант;
— иногда возникают ошибки с передачей переменных byRef, если они не объявлены (то есть надо или объявить переменную, или приписать в функции/процедуре перед ней byVal).

Ещё одним важным оператором является ON ERROR. Привожу варианты:

Возможности языка

Хотя VB довольно прост, полезно почитать документацию по его синтаксису. Я, например, с удивлением узнал, что можно прописывать сложные усолвия в SELECT'ах (аналог switch):

Ускорение работы макросов

Часто макросы требуют долгого времени выполнения, которое можно значительно сократить. В начале и в конце каждой ресурсоёмкой функции вызвать Prepare и Ended.

Важно понимать, что VBA выполняет все действия так же, как и пользователь. Поэтому для того, чтобы установить параметры страницы, он каждый раз открывает и закрывает окно параметров. У меня выставлялись параметры для 10 листов, это реально не быстро. Поэтому делаем так:

Далее, часто нужно просмотреть различные диапазоны ячеек и что-то с ними сделать. Тут важно не использовать циклы for с перебором индексов, они медленные. Можно использовать встроенные функции Экселя, но удобнее всего такой вариант:
Данный код просматривает указанный диапазон, выбирает в нем «специальные ячейки», в данном случае все, в которых есть формулы (т.е. начинаются со знака равно). Для каждой ячейки смотрится, если она не закрашена, то её надо защитить (см. далее) и покрасить. Такой код работает очень быстро.

Для любых переменных, которым вы собираетесь присвоить книгу, лист, диапазон (ячейку) нужно предварительно объявить как Variant.

Естественно, что если вам нужны однотипные значения в ячейках, нужно использовать автозаполнение, всё равно как «растягивание» ячеек пользователем.

Второй диапазон должен включать первый, а второй необязательный параметр указывает тип автозаполнения.

Загрузка книги и события

При открытии книги каждый раз срабатывает процедура.
В данном случае настройки печати (поля, ориентация) сбрасываются на дефолтные. Можно и другую инициализацию выполнять. Важно, что если макросы отключены, то и не выполнится ничего. Если в Экселе вылезла вверху панелька с предупреждением о макросах и пользователь нажал «Включить», то именно в этот момент выполнится процедура Workbook_open().

Список доступных событий можно посмотреть вверху редактора VB. Например, я делал на событие Change проверку, где лежит ячейка, в которой было изменения, и если это нужный диапазон, то делалась запись в лог со старым и новым значением.

Защита

Во-первых сразу отмечу, что MS Office не исполняет макросы на компьютерах, где он не нашел антивируса, если книга зашифрована. Сталкивался на компьютерах, где антивирус был, но видимо Windows XP об этом не знала.

Ещё антивирус может странным образом мешать работе, вызывать ошибки, не совсем объяснимые. Показал айтишникам, сказали ок, что-то сделали, не знаю.

Итак, нам надо защитить книгу, чтобы ввод был разрешен только в нужные ячейки (формулы и заголовки поменять нельзя). Во-первых, нужно сделать соответствующие ячейки «не защищенными». Для этого делаем одно из:
— выделяем диапазон, формат ячеек, снять галочку «Блокировать ячейку»;
— выводим кнопку «Блокировать ячейку» в быстрый доступ и нажимаем её, очень удобно смотреть на неё чтобы понять, защищена ячейка или же нет;
— а это пригодится, чтобы проверить третий вариант — написать макрос, который снимает защиту с нужных ячеек сам.

Далее нужно защитить лист. На вкладке Рецензирование есть такая кнопка. Окошко просит ввести пароль и установить исключения (что можно будет делать пользователю). К сожалению, список исключений маловат. Самое обидное, что нельзя разрешить сворачивать/разворачивать группы столбцов/строк. Поэтому действуем так, на загрузку книги прописываем:
Знак подчеркивания продолжает логическую строку на следующей физической строке. Итак, здесь мы:
1. Сняли защиту.
2. Включили группировку.
3. Поставили защиту, при этом:
— защита только от юзера, макросы продолжают иметь полный доступ (!), крайне важно;
— разрешили сортировку, фильтрацию и форматирование строк/столбцов (высота/ширина);
— DrawingObject в данном случае снимает защиту с примечаний к ячейкам, может и ещё с чего.

Тут мы сталкиваемся с парой сюрпризов. Во-первых, не все макросы будут работать даже так. Известный баг, ничего не сделаешь. Нельзя вставить строку, например. Приходится снимать и тут же ставить защиту. Если «злоумышленник» в этот момент нажмет ctrl+break, то защита слетит.

Во-вторых, скажем никаким способом нельзя удалять строки (AllowDeletingRows), в которых есть защищенные ячейки, хоть одна. Подробнее вот тут.

Решением (костылем) является добавление кнопки или сочетания клавиш для удаления. Заодно можно проверить, чтобы пользователь не удалил чего не надо. В Workbook_open добавляем:

Теперь процедура будет вызываться при нажатии shift+delete.
Знаю, код некрасивый, простите. Здесь я пытался проверить, что выделена строка, то есть строк там 1, а ячеек не меньше тысячи. Чтобы удалить не то, придется выделить тысячу ячеек начиная не с первого столбца. Далее проверяется имя листа и номера строк. Вместо 50 был расчет последенй строки (ведь их число меняется, если мы их удаляем и добавляем).

Заключение

VBA — весьма глючная вещь, которая позволяет сворачивать горы в MS Office. Многие предприятяи используют модели на Excel годами, и если они сделаны хорошо, то всё работает.

Для изучения VBA подходит он сам, во-первых там хорошая справка. Например, чтобы узнать все варианты что можно разрешить в методе Protect, нажимаем F1, Protect, ввод. И вуаля.

Во-вторых, можно проделать требуемые действия вручную, записав макрос, а потом просмотрев его код. Код будет ужасен (например, при изменении параметров страницы, макрос запишет значения всех параметров и полей, а не только измененного вами), но ответы найдутся. Хотя, например, .AutoFit, который записывается при изменении высоты ячейки по содержимому (двойной клик на границе слева), на самом деле не работает.

Предлагаю знатокам поделиться своим опытом, дать советы в комментариях. Спасибо за внимание, удачных разработок вам.

Поставили пароль на VBAProject и забыли его? Есть файл с макросами, хотите в них разобраться, но проект защищён? Не беда, сломаем за 5 минут.

Пошаговая инструкция:

1. Закройте файл, сделайте его копию на случай Fuck Up.

2. Измените расширение файла .xlsm на .zip (пкм Переименовать или F2):

3. Откройте zip файл, найдите папку xl и в ней файл vbaProject.bin:

4. Перетащите файл vbaProject.bin из архива:

5. Откройте его с помощью Notepad++ :

6. При помощи поиска (Ctrl+F) найдите строку DPB и замените в ней букву B на любую другую, сохраните и закройте файл:

7. Перенесите обновленный файл vbaProject.bin обратно в архив, закройте архив.

8. Измените расширение файла .zip в .xlsm.

9. Откройте файл, в появившемся диалоговом окне нажмите Да:

12. На вкладке Protection уберите галку Lock project for viewing и сохраните файл:

Не сработало? Поможет бесплатная надстройка MACROTools VBA Excel, а надстройка EXCELTools автоматизирует ваши рутинные и монотонные операции в MS Excel.

При желании проект можно поддержать рублем.

Хотите научиться самостоятельно писать макросы и разбираться в VBA, но не знаете с чего начать? Лучший канал на YouTube по этой теме:

MS, Libreoffice & Google docs

465 постов 12.7K подписчика

Правила сообщества

2. Публиковать посты соответствующие тематике сообщества

3. Проявлять уважение к пользователям

4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.

По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях

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

Утверждения вроде "пост - отстой", это оскорбление автора и будет наказываться баном.

Dim i As Integer, j As Integer, k As Integer

Dim l As Integer, m As Integer, n As Long

Dim i1 As Integer, i2 As Integer, i3 As Integer

Dim i4 As Integer, i5 As Integer, i6 As Integer

Dim kennwort As String

On Error Resume Next

For i = 65 To 66: For j = 65 To 66: For k = 65 To 66

For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66

For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66

For i5 = 65 To 66: For i6 = 65 To 66

kennwort = Chr(i) & Chr(j) & Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6)

For n = 32 To 126

ActiveSheet.Unprotect kennwort & Chr(n)

MsgBox "Done in " & Format(Timer - t, "0.0 sec")

Next: Next: Next: Next: Next: Next

А как вот такую защиту снять?

Блин, давным давно уже макрос придуман, снимает любой пароль с проекта за секунду. А способ с архивом в большинстве случаев не работает. Этот метод даст снять пароль с Майкрософтовского аддона "поиск решения"?
/он тоже в вба висит как макрос. Эксель версий 2000-2005

С нуля за 5 часов можно научиться всему.

А так сатану случайно не призовешь? А то очково как-то

Мда. открыл ящик Пандоры ))) болтались интересные программы, теперь они не только интересные но и рабочие )

Сам способ кстати интересный. Но я редактировал этот файл с помощью 16ти-ричного редактора HEX+ вроде, и менял другие символы. Делал в декабре 19, надо освежить

а есть способ с винрар архива пароль снять?

А можно пароль снять, которым лист защищен от изменений?

там вроде стойкое шифрование в последних версиях, Elcomsoft Advanced pasword recovery может днями ковырять, если пароль сложный и длинный. если например из одних цифр - то за полчаса или час

Пару раз пробовал. получалось даже с вордовским запароленым файлом

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

Легла в направлении мечты => Пост №2

Так же мне посоветовали посмотреть конференции DevGamm. Как раз хорошо совпало, что такая конференция проходила онлайн с 17-19 ноября. Если кому интересно, у них имеется так же ютуб канал, где рассказывают про игры. При чем здесь имеется все: и про арт, и про маркетинг и конечно про сами игры, слушала как разработчики представляют свои работы. Очень интересно!

По английскому языку, больше делаю упор на сайт inSpeak. Так как в комментах подсказали, что лучше делать упор на разговорную речь и понимание что тебе говорят английском языке. Так же начала слушать аудио книги на Английском, их можно скачать на сайте Lelang, очень прикольно. Можно скачать аудио книгу и pdf к ней.

По книгам, добавила в свой список “Грокаем алгоритмы”, думаю к ней смогу приступить как раз после ООП.

Полный список моей программы можно посмотреть в первом посте: Легла в направлении своей мечты

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

Было очень интересно прочитать в комментариях истории у кого всё получилось, и они уже в разработке. Особенно что и после 30 можно попасть в геймдев. Спасибо всем, кто поддерживает. Те, кто считает, что у меня ничего не выйдет и я все брошу, тоже спасибо, вы очень мотивируете писать посты, а не бросать их.

Ответ на пост «Ответ на пост *разочарование '»

Вы молодец. Действительно. Сейчас расскажу, почему я так считаю:

Вспоминаю 92-93 годы. У отца на работе был рабочий компьютер. Выдали по должности, звание на тот момент не помню какое, кэпом был то ли третьего ранга, то ли второго. Камчатка, в\ч 90129. Он его мне домой тогда припер, типа разбирайся. Комп древний даже по тем меркам: 286 XT с монохромным CGA/Hercules с матричным принтером Epson FX-800. Читал со словарем мануал от принтера, настроил DIP микропереключателями русский язык на печать. Купил на свои заработанные деньги две дискеты 5`25 (по 100рублей, TDK, помню) - ходил по знакомым (в Петропавловске-Камчатском в то время 12 летнему пацану это было сложно), скопировал себе Basic и несколько игр. Взял в библиотеке книгу детскую по Бейсику, ели нашел.

Завел тетрадку, туда всё переписывал. До этого ходил на курсы в компьютерный клуб, изучил на Ямахах MSX язык Лого, так что опыт был.

Зашел как-то разговор с отцом, что у него сидят 10 или 12 тетенек-бухгалтеров и считают на калькуляторах и счетах нормы выдачи военнослужащим разных продуктов. Точно сейчас не помню, но смысл был такой: военная часть, на каждую единицу л\с определенная норма продуктов. На кораблях чуть по другому, на лодках тоже иначе. Частей военных нашем регионе было много, кораблей много, лодок хватало. Я написал программу, которая выводила на печать то, что эти тетеньки считали вручную. И за пару недель всё сделал. Отец отнес распечатки в бухгалтерию, те проверили и ОКАЗАЛОСЬ, что всё точно))

Не знаю уж как, но мне выплатили зарплату в 10000 рублей. Официально никак, но потом отец рассказывал, что тетки вроде сами передали мне. Сколько это точно не помню, но хлеб стоил на Камчатке около 50р, машину помыть стоило 100-200р, газету с программой ТВ я покупал в типографии за 4р, а продавал по 10р)) Ну и кроме этого у меня появился новый комп c цветным VGA монитором с принтером Epson FX-1000, который стоил как Жигули новые, но это совсем другая история.

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

Защита листа Excel не относится к параметрам безопасности. В действительности, она предназначена не для ограничения доступа посторонних лиц к данным защищенного листа. Значит ли тот факт, что Microsoft Excel использует очень простой алгоритм шифрования для защиты листов, что защита Excel бесполезна? Вовсе нет! Данная мера предназначена для блокировки листов с целью защиты от внесения случайных изменений в их содержимое. В данной статье мы подробно расскажем Вам, как удалить защиту ячеек Excel.

Почему пользователи хотят снять защиту ячеек Excel?

Как снять защиту ячеек в файле Excel?

Способ 1: Удаление защиты ячеек Excel с помощью известного пароля

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

Ниже представлена простая инструкция, как удалить защиту с выделенных ячеек в Excel:

1. Выделите ячейку, которую Вы хотите изменить. При выборе дополнительных ячеек зажмите клавишу "Ctrl".

2. Перейдите на вкладку "Главная".

3. В разделе "Ячейки" выберите "Формат" > "Формат ячеек".

4. Выберите вкладку "Защита", а затем уберите отметку в пункте "Защищаемая ячейка".

5. Согласно информации в диалоговом окне, блокировка ячеек или скрытие формул не имеет никакого эффекта, пока Вы не защитите лист. Вы можете сделать это во вкладке "Рецензирование", выбрав опцию "Защитить лист". Нажмите "ОК".

6. Весь лист теперь будет защищен, за исключением разблокированных Вами ячеек.

Если Вы хотите редактировать защищенный паролем лист, но не помните пароль, попробуйте снять защиту с листа с помощью следующих способов.

Способ 2: Как убрать защиту ячеек Excel с помощью программы Zip

Данный способ работает только для файлов Excel в формате .xlsx. Поэтому, если Ваша книга Excel сохранена в формате .xls, просто откройте ее и сохраните как .xlsx.

1. Измените расширение файла Excel с .xlsx на .zip. Затем нажмите "Да" в появившемся запросе.

2. Откройте архив ZIP с помощью программы 7-ZIP. Откройте папку xl->worksheets, и Вы увидите файлы sheet1.xml, sheet2.xml, sheet3.xml. Если Вы хотите снять защиту sheet1, выберите файл sheet1.xml. Нажмите на него правой кнопкой мыши, выберите опцию "Редактировать" и откройте с помощью Блокнота.

3. Удалите тэг, начиная со слова "Protection". Затем сохраните и закройте файл sheet1.xml.

4. Обновите измененный файл sheet1.xml в архиве ZIP в появившемся запросе. Затем закройте архив ZIP.

5. Измените расширение файла ZIP обратно на .xlsx. Теперь на листе Excel не установлена защита. Откройте его, и Вы сможете редактировать лист без необходимости ввода пароля.

Способ 3: Как удалить защиту ячеек Excel с помощью программы VBA (для версии 2010 и ниже)

1. Откройте Ваш документ Excel и перейдите на защищенный паролем лист.

2. Нажмите сочетание клавиш Alt + F11, чтобы открыть Visual Basic Editor.

3. Правой кнопкой мыши нажмите на имя книги на левой панели (Панель проекта VBA) и выберите "Insert" ("Вставить") > "Module" ("Модуль").

4. В новом окне вставьте следующий код.

5. Нажмите клавишу F5 или выберите опцию "Run" ("Запустить") на панели инструментов, а затем подождите несколько минут.

6. Макрос предоставит взломанный пароль, который не является оригинальной комбинацией (обычно комбинации букв A и B), но, тем не менее, работает. Просто нажмите "ОК", и защита листа будет снята.

Однако помните, что если Ваш файл содержит более одного защищенного листа, Вам придется запускать макрос отдельно для каждого листа.

Чтобы удалить защиту листа Excel без пароля в современных версиях Excel 2013 и Excel 2016, Вы можете сначала сохранить документ как книгу Excel 97-2003 (*.xls), запустить макрос, чтобы удалить защиту, а затем снова сохранить книгу как файл .xlsx. Либо Вы можете использовать один из остальных трех способов, например, программу для восстановления паролей к файлам Excel.

Способ 4: Как снять защиту с ячеек Excel с помощью программы от PassFab

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

Шаг 1. Загрузите данную программу для восстановления паролей к файлам Excel xls/xlsx на Ваш компьютер.

Шаг 2. Откройте программу, и Вы увидите главное меню программы. Нажмите на кнопку «Пожалуйста, импортируйте файл!», чтобы импортировать заблокированный лист Excel, пароль к которому Вы хотите восстановить.

Шаг 3. Выберите тип атаки на пароль и задайте соответствующие параметры. Доступны 3 режима атаки: атака по словарю, атака по маске и атака грубой силой.

Step 4. Запустите восстановление потерянного пароля к Вашему файлу Excel. Для этого нажмите "восстановить".

Заключение

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

Читайте также: