Не мешающие работе методы защиты формул листа (делюсь опытом)

[Alex_ST]

Метод 1.
Просматривая очень интересную книжку "Excel. Трюки (100 профессиональных приёмов)" авторов Р.Холи и Д.Холи, наткнулся на очень интересный способ защиты формул листа от кривых рук невнимательных юзеров - использование проверки данных. На основе предложенного в книге "ручного" метода слепил макрос:

Код: (vb)

Sub Formula_Protect_with_CellValidation()
        ActiveWindow.RangeSelection.SpecialCells(xlCellTypeFormulas).Select
        With Selection.Validation
            .Delete
            .Add Type:=xlValidateCustom, Formula1:="="""""
            .ErrorTitle = "ОШИБКА!"
            .ErrorMessage = "В ячейке формула!" & vbCrLf & "Ввод данных запрещён!"
            .ShowError = True
        End With
End Sub

Защищает ячейки с формулами, расположенные на активной странице, от случайного ввода данных с клавиатуры.
Не защищает от копи-паста (т.к. тогда и условия проверки ячейки тоже заменяются).
К стати, Select начале кода можно и убрать, но тогда будет не видно, что макрос отработал и защитил ячейки.
И к тому же от Select есть ещё один побочный плюс: если ячейки с формулами окажутся после отработки макроса выделенными, то с них будет легко и снять проверку при необходимости что-то подправить в формулах.

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

[Alex_ST]

Метод 2.
В той же книжке нарыл и "по мотивам" сделал защиту формул ещё одним методом
1. В стандартном модуле (можно в Personal) пишете процедуру, которая сделает "беззащитными" все ячейки листа кроме ячеек с формулами:

Код: (vb)

Sub Formula_Protect_with_SheetProtection()
    With ActiveSheet: .Unprotect: .Cells.Locked = False: .Cells.FormulaHidden = True: .EnableSelection = xlNoRestrictions: End With
    ActiveWindow.RangeSelection.SpecialCells(xlCellTypeFormulas).Locked = True
End Sub

2. В модуле листа пишете процедуру включения защиты листа при выборе защищаемой ячейки:

Код: (vb)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Me.Unprotect: Me.EnableOutlining = True
    If Target.Locked Then Me.Protect Contents:=True, Scenarios:=True, UserinterfaceOnly:=True, AllowFiltering:=True
End Sub

Данный способ будет работать только если у юзверга включены макросы.
Зато покоцать формулу делитом и копи-пастом не даст!

[TofigSTC]

Посмотрите 9-й трюк.....
Но! У меня он не сработал после открытия книги, т.е. все нормально работало, именно так как и нужно - кроме диапазона с формулами, весь остальной лист был доступен.... После сохранения, закрытия и последующеяо открытия код почему-то не сработал......

[Alex_ST]

Посмотрите 9-й трюк...

1. Если это приказ, то на каком основании? Если это заказ, то на каких условиях? Если это просьба о помощи, то почему так сформулирована?
2. Говоря так, Вы должны либо привести выдержку из текста книги, либо приложить ссылку для ознакомления с "трюком" всех интересующихся
3. В разделе "Трюк №9. Блокировка и защита ячеек, содержащих формулы" описано ТРИ метода.
На основании второго и третьего я написал и выложил выше макросы.

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

В первом методе никакого кода нет, поэтому не сработать он не мог в принципе. Так же, впрочем, как и сам метод не мог не сработать.
4. Более подробно изложите, что и в какой последовательности Вы делали и что не получилось.
Выложите всой фай, в котором не работает установленная Вами защита. Посмотрим.
_______________________________________________________
P.S. Ссылки для скачивания книги «Excel. Трюки» Холи Р., Холи Д. можно взять ЗДЕСЬ.
Для не читавших книгу: первый метод - это установить защиту только ячеек с формулами. Для этого предлагается:
- сделать все ячейки не защищаемыми
- по F5->"Выделить..." выбрать ячейки с формулами
- сделать выбранные ячейки защищаемыми
- поставить защиту листа

[TofigSTC]

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

[Alex_ST]

TofigSTC,
давайте попробуем разобраться без обид.
Вы слишком не внятно задали вопрос, предварив его без разрыва ответом на вопрос. Потому и непонятки.

Я так понял из Вашего крайнего поста, что Вы использовали защиту формул макросами по моему Варианту2?
Тогда опишите:
1. Какую из двух процедур и куда Вы положили.
2. Как включали защиту формул.
3. Что и когда сработало, а что и когда нет.

[TofigSTC]

Ну что Вы, какие тут могут быть обиды?
Я использовал третий способ описанный в книге, трюк 9... Правда у меня английский вариант ... В русской версии - издательство Питер, 2005 год, стр 46-47.... Третий способ описывает блокировку диапазона с использованием кода. Его я и вставил в модуль листа, но предварительно, чтобы этот метод сработал необходимо - "Для начала удостоверьтесь, что заблокированы только те ячейки, которые вы хотите защитить, а все остальные не
заблокированы" -Это цитата из книги.....

Все нормально работает ДО закрытия.... Открываю книгу - все......

[Alex_ST]

Все нормально работает ДО закрытия.... Открываю книгу - все...

Ну, значит что-то сбрасывает защиту листа при сохранении, закрытии или открытии книги.
Проверьте, что у Вас происходит при этих событиях (проще всего это сделать в пошаговом режиме, прописав инструкции Stop в Workbook_BeforeClose и Workbook_Open.

Вполне может быть, что работает и "гадит" какая-то надстройка.
Проверьте, работает ли установленная защита после сохранения книги.
Если работает, то очень хорошо бы проверить, осталась ли защита книги при открытии её на другом компьютере.
Или отключите у себя все надстройки и попробуйте открыть книгу.
Если после сохранения не работает, то проходите пошагово Workbook_BeforeSave

[TofigSTC]

"Проверьте, работает ли установленная защита после сохранения книги" - Так весь сыр-бор именно в том и заключается, что не работает т.е. открываешь сохраненную книгу и все....
Спасибо, проверю по Вашим советам

[Alex_ST]

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

[Rama]

http://rghost.net/49646011
Можно ли помочь собрать надстройку ? Хоть убей, не получается, а ЗДЕСЬ помочь некому, все пытаются научить по своему.
Есть форма, на нее нужно наложить макрос в модуле (возможно его придется чуть исправить)

[Дмитрий Щербаков(The_Prist)]

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

[Alex_ST]

Можно ли помочь собрать надстройку ? Хоть убей, не получается, а ЗДЕСЬ помочь некому, все пытаются научить по своему.
Есть форма, на нее нужно наложить макрос в модуле (возможно его придется чуть исправить)

ПОМОЧЬ-то было бы, наверное, и можно.
Но судя по дискуссии на Планете, на которую Вы дали ссылку, Вы хотите, чтобы это кто-нибудь СДЕЛАЛ ДЛЯ ВАС.
Разницу улавливаете?
На Планете Вам долго пытались объяснить как сделать то, что Вы хотите, т.е. именно ПОМОГАЛИ
Не помогло...
Здесь, у Дмитрия, так же, как и на нескольких других форумах, между прочим, общается большинство форумчан с Планеты.
Там Вы уже "засветились". Здесь теперь тоже вряд ли кто-нибудь захочет попусту терять время.

[Юрий М]

Алекс, я не только объяснил - я даже сделал форму и показал - КАК действовать дальше)))

[Kroha1954]

Уважаемый Alex_ST!
Очень полезный макрос, начал использовать Метод 1. Работает прекрасно, но возникла проблема: не понимаю как его можно отключить, даже при удалении макроса выдается сообщение об ошибке и не дает ничего вводить в ячейку с формулой. Очень прошу проконсультируйте, что нужно делать. Надеюсь на понимание и ответ.