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

[Alex_ST]

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

Код Выделить Развернуть

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]

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

Код Выделить Развернуть

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. В модуле листа пишете процедуру включения защиты листа при выборе защищаемой ячейки:

Код Выделить Развернуть

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

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

[ZORRO2005]

На форуме была тема защиты формул без макросов с помощью проверки данных.
Достаточно ввести какой-нибудь знак в поле "Формула:"

Этот способ не защитит от COPY-PASTE и DEL.

[Alex_ST]

ZORRO2005,
ну так ведь принцип действия первого метода именно на проверке данных и основан.
Макрос нужен только для того, чтобы "одним кликом" проделать все эти операции со всеми ячейками листа, содержащими формулы.
А сам макрос никакого отношения к действию защиты не имеет. Он просто упрощает её установку.
Макрос у  меня, например, лежит в Personal.xls и к нему "прицеплена" кнопка на панели управления.

[ZORRO2005]

ну так ведь принцип действия первого метода именно на проверке данных и основан.

Согласен, но я написал для тех, кто с макросами не дружит и для них непонятно, что

Код Выделить Развернуть

Add Type:=xlValidateCustom - это проверка данных.
Да, и вдруг кто-то захочет заменить "" на свою фамилию.