Защита ячеек от изменения Excel

     Возможно кому-то это будет и не интересно, но наверное всё-таки найдутся и те, кому эта информация будет полезной.  Речь пойдёт о цикле статей по работе в Office Excel 2007 + офисный VBA (Visual Basic Application). Сразу отмечу, что Excel + VBA я стал изучать не так давно, и по мере работы у меня возникали различные вопросы и трудности. Сейчас эти “трудности” превратились лишь в весёлые воспоминания =) Я придерживаюсь принципа “Глупый человек учиться на своих ошибках, Умный – на чужих”  и поэтому я решил помочь Вам не допускать тех же ошибок, с которыми пришлось сталкиваться мне. Надеюсь предложенная информация пригодиться новичкам, а профи не будут меня сильно бить палками. Ну и на последок, мне не хотелось бы чтобы все эти статьи были в стили монолог – автора, поэтому буду рад пообщаться с вами в комментариях, вопросы приветствуются и поощряются(если у вас есть блог, то в посте-ответе будет стоять ссылка на ваш сайт). Ладно, хватит “лить воду”, пора приступить к первому совету, думаю вы уже из названия топика догадались, что речь пойдёт о Защите ячеек от изменений.

     В чём же может возникнуть трудность с защитой ячеек в Excel. Во-первых во вкладке “рецензирование” в меню “изменения” есть кнопка “защитить лист”. 

priventcell

     Отметим галочку на “защитить лист и содержимое защищаемых ячеек”, далее введём пароль, остальные checkbox’ы оставим без галочек, нажмём “OK”, потом подтвердим пароль и вот все ячейки листа стали заблокированными.  Отмечу, что по умолчанию в Excel  при создание нового листа, у всех ячеек  в свойствах “формат ячеек” – “защита” отмечено “защищаемая ячейка”. Так что если Вы захотите, чтобы какая-то ячейка была не защищенная, т.е. была доступна для изменений, то всего лишь уберите эту галочку. После этого и возникнут трудности, во всяком случае у меня возникли… 

     Итак, у меня была таблица, в которой находились ячейки доступные для изменений, а также ячейки в которых информация вычислялась автоматически (к примеры сумма чисел) – и они должны быть защищены от изменений. В принципе в этом нет ничего трудного, с одних ячеек(доступных для ввода данных) убираем ”защищаемая ячейка”, на других(вычисления в которых происходят автоматически) оставляем. Жмём “защитить лист” и радуемся результату.

easyprivent

     Изменяя значения в ячейках E7, F7,G7 в ячейке M7(нельзя выделить) вычисляется их сумма. НО…не всё так просто. Если в защищенных ячейках значение вычисляется по стандартным формулам Excel из меню “формулы”, то проблем не будет, а вот если вы написали макрос на VBA для вычисления более сложных значений, то при запуске этого макроса у вас появится ошибка

error1004

      Что же делать в этом случае?  Первое что мне пришло в голову, сейчас многие из вас будут смеяться =) это оставить все ячейки незащищенными, а далее над ячейками значения в которых вычисляются автоматически разместить Вставка – Фигуры – Прямоугольник..в свойствах которого установить Прозрачность 100%… Таким образом значения в ячейках будут видны, но наведя на них курсор мышки Вы не сможете их выделить и изменить… Минус такой “фишки” в том, что продвинутый пользователь “может догадаться” воспользоваться  клавишами “стрелки” на клавиатуре, и попав в ячейку, которую мы “прикрыли” прозрачным прямоугольником, изменить в ней значения. Этого как раз нам свами больше всего и не хотелось…

     Выход из данной ситуации:

     Ячейки, информация в которых должна быть не доступна для изменений, мы  оставляем защищенными. Далее в макросе, который производит вычисление по хитрой формуле, прописываем Worksheets(”Имя листа”).Unprotect Password:=”пароль” …текст макроса… Worksheets(”Имя листа”).Protect Password:=”пароль”

Напоследок пример такого макроса:

        Private Sub CommandButton1_Click()
        Worksheets(”Gualanland”).Unprotect Password:=”gualan”
        Range(”A3″).Value = Range(”A1″).Value * Val(Range(”A2″).Value)

        Worksheets(”Gualanland”).Protect Password:=”gualan”
        End Sub

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

© Блог гордого владельца говносайтов – автор текста gualan.

Категория: Excel
Вы можете следить за изменениями этой записи через RSS 2.0 фид. Вы можете оставить комментарий, или трэкбек с вашего сайта.
19 Ответов
  1. Myledi:

    Gualan!!!
    Полный респект и уважуха.Вы-прелесть.Измучилась вся — искала решение проблемы с защитой листа «от дурака».Вы спасли мои формулы от ежедневного»убийства».Огромное СПАСИБО.Теперь у меня работают макросы и ячейки защищены.Просто СУПЕР!Еще раз спасибо!

  2. Большое пожалуйста.. рад что помогло!

  3. Макс:

    Thanks////////////////// Это ооооооооооооооочень нужная инфа. Ещё раз спасибо

  4. Зовите меня Вася:

    помогло, легко и просто
    спасибо, потом посмотрю как это работает в ВБА

  5. Oleg:

    Поставил но после применения макроса ячейки становятся незащищенными ПРОБЛЕМА !!!

  6. Oleg, а вы точно в последней строчке макроса не забыли Worksheets(”имя вашего листа”).Protect Password:=”ваш пароль”??

  7. Serega:

    Спасибо за статью выручил!

  8. Andy Harder:

    Спасибо за совет!

  9. Восхищаюсь Вами, какая Вы умница!!! Наконец я защитила свои формулы. Дай Вам Бог здоровья!

  10. Kafer:

    Штука прикольная (и автору спасибо, вопрос часто есть актуальный), но есть одно но: при долгом выполнении макроса комбинацией ctrl+break можно его прервать и таким образом оставить лист незащищенным. Лучше распароливать и защищать лист на небольших участках кода. ИМХО :)

  11. Kafer, спасибо за замечание, как-то я об этом и не подумал :)

  12. Игорь:

    Большое спасибо, очень помогло.

  13. дддд:

    спасибо,полезная информация

  14. Виктор:

    Скиньте кто-нибудь ваш файл с защитой, кто 100% уверен что не сломать, я ломал за 15 минут такой excel, результат вышлю обратно. Просто друг много способов перепробовал, а все равно ломал, ему нужно тоже защитить листы с формулами… У кого не сломаю, буду даже рад за оказанную помощь) и действительно полезная вещь будет…

  15. Виктор, взломать файл не проблема.. было бы здорово, если бы вы могли не взломать его, а узнать пароль… в интернете можно легко найти программку, которая подберет хеш пароля от excel и файл откроется без проблем… остается вопрос, как же его обратно запаролить старым паролем, чтобы никто не догадался, что в него внесены изменения :)

  16. Сергей:

    А как это делать в Excel 10? Выходит такая же ошибка.
    Вот формула моего макроса:

    Sub Норм2()

    ‘ Норм2 Макрос

    ‘ Сочетание клавиш: Ctrl+в

    Columns(«C:C»).Select
    Selection.AutoFilter
    ActiveSheet.Range(«$C$1:$C$1098″).AutoFilter Field:=1, Criteria1:=»Норм.»

    End Sub

  17. Анастасия:

    gualan!

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

  18. Анастасия, я так понимаю проблема в том, что вам нужно разрешить пользователям изменять содержимое ячеек, но вы хотите запретить изменять цвет, шрифт и т.п.? Стандартным средствами, насколько я знаю, такого не сделать, но можно написать макрос, который будет постоянно сбрасывать все настройки ячеек на «по умолчанию», и привязать этот макрос например к SelectionChange…

Оставить комментарий