Новости:

Теперь на форум можно залогиниться / зарегистрироваться с помощью ВКонтакте. Уже существующие пользователи могут связать свою учетную запись с аккаунтом ВКонтакте одним кликом в профиле пользователя http://forum.msexcel.ru/index.php?action=profile;area=account

Главное меню

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

Автор Alex_ST, 21.11.2012, 13:04

« назад - далее »

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.
Яндекс-деньги: 410011658492153

Alex_ST

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

ZORRO2005

Цитата: Alex_ST от 22.11.2012, 20:47
ну так ведь принцип действия первого метода именно на проверке данных и основан.
Согласен, но я написал для тех, кто с макросами не дружит и для них непонятно, что
Add Type:=xlValidateCustom - это проверка данных.
Да, и вдруг кто-то захочет заменить "" на свою фамилию.
Яндекс-деньги: 410011658492153