Просматривая очень интересную книжку "Excel. Трюки (100 профессиональных приёмов)" авторов Р.Холи и Д.Холи, наткнулся на очень интересный способ защиты формул листа от кривых рук не внимательных юзеров - использование проверки данных. На основе предложенного в книге "ручного" метода слепил макрос: [vba]
Code
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
[/vba] Защищает от случайного ввода данных в ячейки с формулами с клавиатуры. Не защищает от копи-паста (т.к. тогда и условия проверки ячейки тоже заменяются). Вот только никак не соображу, можно ли сделать, чтобы совсем стереть формулу было нельзя Хотя это и не очень принципиально, т.к. стирание - это уже будет не случайный ручной ввод данных, а намеренная порча, а с такими юзвергами уже и разборки должны быть другими
Просматривая очень интересную книжку "Excel. Трюки (100 профессиональных приёмов)" авторов Р.Холи и Д.Холи, наткнулся на очень интересный способ защиты формул листа от кривых рук не внимательных юзеров - использование проверки данных. На основе предложенного в книге "ручного" метода слепил макрос: [vba]
Code
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
[/vba] Защищает от случайного ввода данных в ячейки с формулами с клавиатуры. Не защищает от копи-паста (т.к. тогда и условия проверки ячейки тоже заменяются). Вот только никак не соображу, можно ли сделать, чтобы совсем стереть формулу было нельзя Хотя это и не очень принципиально, т.к. стирание - это уже будет не случайный ручной ввод данных, а намеренная порча, а с такими юзвергами уже и разборки должны быть другими Alex_ST
С уважением, Алексей MS Excel 2003 - the best!!!
Сообщение отредактировал Alex_ST - Вторник, 06.11.2012, 20:04
Select в принципе можно и убрать, но тогда будет не видно, что макрос отработал. И к тому же от Select есть ещё один побочный плюс: если ячейки с формулами окажутся выделенными, то с них будет легко и снять проверку при необходимости что-то подправить в формулах. Я вообще-то сам сначала сделал без Select, но потом поюзал и решил, что его намного удобнее оставить.
Select в принципе можно и убрать, но тогда будет не видно, что макрос отработал. И к тому же от Select есть ещё один побочный плюс: если ячейки с формулами окажутся выделенными, то с них будет легко и снять проверку при необходимости что-то подправить в формулах. Я вообще-то сам сначала сделал без Select, но потом поюзал и решил, что его намного удобнее оставить.Alex_ST
С уважением, Алексей MS Excel 2003 - the best!!!
Сообщение отредактировал Alex_ST - Вторник, 06.11.2012, 20:07
В той же книжке нарыл и "по мотивам" сделал защиту формул ещё одним методом 1. В стандартном модуле (можно в Personal) пишете процедуру, которая сделает "беззащитными" все ячейки листа кроме ячеек с формулами:[vba]
Code
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
[/vba] 2. В модуле листа пишете процедуру включения защиты листа при выборе защищаемой ячейки:[vba]
Code
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Me.Unprotect If Target.Locked Then Me.Protect End Sub
[/vba]
Данный способ будет работать только если у юзверга включены макросы. Зато покоцать формулу делитом и копи-пастом не даст!
В той же книжке нарыл и "по мотивам" сделал защиту формул ещё одним методом 1. В стандартном модуле (можно в Personal) пишете процедуру, которая сделает "беззащитными" все ячейки листа кроме ячеек с формулами:[vba]
Code
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
[/vba] 2. В модуле листа пишете процедуру включения защиты листа при выборе защищаемой ячейки:[vba]
Code
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Me.Unprotect If Target.Locked Then Me.Protect End Sub
[/vba]
Данный способ будет работать только если у юзверга включены макросы. Зато покоцать формулу делитом и копи-пастом не даст!Alex_ST
С уважением, Алексей MS Excel 2003 - the best!!!
Сообщение отредактировал Alex_ST - Вторник, 06.11.2012, 13:43
К стати, наткнулся на "бяку": если при втором способе защиты выделена ячейка с формулой, то, естественно, включается защита листа по умолчанию и оказываются невозможными группировка/разгруппировка строк/столбцов (понижение/повышение уровня) и применение фильтров. Конечно, можно и другую ячейку выбрать после того, как тебя "обругают", что ячейка защищена, но лучше этого просто избежать, изменив в модуле листа процедуру так:[vba]
Code
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
[/vba]
К стати, наткнулся на "бяку": если при втором способе защиты выделена ячейка с формулой, то, естественно, включается защита листа по умолчанию и оказываются невозможными группировка/разгруппировка строк/столбцов (понижение/повышение уровня) и применение фильтров. Конечно, можно и другую ячейку выбрать после того, как тебя "обругают", что ячейка защищена, но лучше этого просто избежать, изменив в модуле листа процедуру так:[vba]
Code
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
Я давно искал способ облегчённой защиты - что-то вроде лёгкой брони, а всё оказалось так просто! Вот спасибо! Поэкспериментировал с таким результатом: в ручном режиме в любую защищаемую ячейку (с константой или формулой) в окне проверки достаточно записать один знак = (больше ничего!)
Я давно искал способ облегчённой защиты - что-то вроде лёгкой брони, а всё оказалось так просто! Вот спасибо! Поэкспериментировал с таким результатом: в ручном режиме в любую защищаемую ячейку (с константой или формулой) в окне проверки достаточно записать один знак = (больше ничего!) v__step
С уважением, Владимир
Сообщение отредактировал v__step - Суббота, 05.01.2013, 23:55
Пожалуйста. Но мне всё-таки больше нравится второй способ, т.к защищает ячейки с формулами от делита и копи-паста. А макросы у нас на работе у всех включены по умолчанию, т.к. приходится работать с листами-заданиями, шаблон которых я слепил лет 12-14 назад, используя комбобоксы, потому что тогда ещё не знал о возможности использования именованных диапазонов для списков проверки данных А потом Ай-Ти-шники уже сделали импорт из базы данных в такие шаблоны. Так что переделывать не имеет смысла.
Цитата (v__step)
Вот спасибо!
Пожалуйста. Но мне всё-таки больше нравится второй способ, т.к защищает ячейки с формулами от делита и копи-паста. А макросы у нас на работе у всех включены по умолчанию, т.к. приходится работать с листами-заданиями, шаблон которых я слепил лет 12-14 назад, используя комбобоксы, потому что тогда ещё не знал о возможности использования именованных диапазонов для списков проверки данных А потом Ай-Ти-шники уже сделали импорт из базы данных в такие шаблоны. Так что переделывать не имеет смысла.Alex_ST
А у меня задача прямо противоположная: каждый столбец соответствует расчётной ситуации, и мы только и делаем, что плодим их Теперь сбылась моя мечта , и формулы получили защиту, при этом копирование не запрещено
А у меня задача прямо противоположная: каждый столбец соответствует расчётной ситуации, и мы только и делаем, что плодим их Теперь сбылась моя мечта , и формулы получили защиту, при этом копирование не запрещеноv__step
С уважением, Владимир
Сообщение отредактировал v__step - Понедельник, 07.01.2013, 00:41
И к тому же от Select есть ещё один побочный плюс: если ячейки с формулами окажутся выделенными, то с них будет легко и снять проверку при необходимости что-то подправить в формулах. Я вообще-то сам сначала сделал без Select, но потом поюзал и решил, что его намного удобнее оставить.
Шикарная защита от "дурака" - прям то что доктор прописал! После 5-6 часов почти непрерывного вбивания данных в таблицы, запросто можно непреднамеренно "колупнуть" ячейки с формулами - прецеденты случаются и уследить за этим почти невозможно. Спасибо за труд и разъяснения! Правда пришлось попыхтеть, почитать, поэкспериментировать часа 2-3 - я пользователь лишь слегка продвинутый. Все сделал и все работает! Остался открытым лишь один вопрос: как вот это сделать "если ячейки с формулами окажутся выделенными, то с них будет легко и снять проверку при необходимости что-то подправить в формулах"? Я честно бился 2 часа подряд, но ничего у меня не получается. P.S. А можно как-нибудь в этом варианте сделать(написать) исключение для 1-2-ух определенных столбцов в таблице и оставить их без защиты?
И к тому же от Select есть ещё один побочный плюс: если ячейки с формулами окажутся выделенными, то с них будет легко и снять проверку при необходимости что-то подправить в формулах. Я вообще-то сам сначала сделал без Select, но потом поюзал и решил, что его намного удобнее оставить.
Шикарная защита от "дурака" - прям то что доктор прописал! После 5-6 часов почти непрерывного вбивания данных в таблицы, запросто можно непреднамеренно "колупнуть" ячейки с формулами - прецеденты случаются и уследить за этим почти невозможно. Спасибо за труд и разъяснения! Правда пришлось попыхтеть, почитать, поэкспериментировать часа 2-3 - я пользователь лишь слегка продвинутый. Все сделал и все работает! Остался открытым лишь один вопрос: как вот это сделать "если ячейки с формулами окажутся выделенными, то с них будет легко и снять проверку при необходимости что-то подправить в формулах"? Я честно бился 2 часа подряд, но ничего у меня не получается. P.S. А можно как-нибудь в этом варианте сделать(написать) исключение для 1-2-ух определенных столбцов в таблице и оставить их без защиты?Serge1400
Сообщение отредактировал Serge1400 - Суббота, 02.07.2016, 19:43
как вот это сделать "если ячейки с формулами окажутся выделенными, то с них будет легко и снять проверку при необходимости что-то подправить в формулах"?
1. Выделяете необходимый диапазон ячеек (если нужны все ячейки на листе, то достаточно ОДНУ ячейку, все не обязательно) 2. Давите F5 - получаете 3. Кликаете на "Выделить" - получаете 4. Выбираете то, что хотите выделить, и давите на ОК. 5. Радуетесь
как вот это сделать "если ячейки с формулами окажутся выделенными, то с них будет легко и снять проверку при необходимости что-то подправить в формулах"?
1. Выделяете необходимый диапазон ячеек (если нужны все ячейки на листе, то достаточно ОДНУ ячейку, все не обязательно) 2. Давите F5 - получаете 3. Кликаете на "Выделить" - получаете 4. Выбираете то, что хотите выделить, и давите на ОК. 5. Радуетесь Alex_ST
А можно как-нибудь в этом варианте сделать(написать) исключение для 1-2-ух определенных столбцов в таблице и оставить их без защиты?
А просто не выделять эти столбцы перед вызовом макроса защиты - для Вас не вариант?
Тогда, извините, это не ко мне, т.к. я всегда пишу УНИВЕРСАЛЬНЫЕ макросы, не привязанные к именам книг, листов, конкретным диапазонам, столбцам, строкам, ячейкам Универсальный инструмент - это для всех, а специальный делается по заказу. По заказам же я практически никогда не пишу.
А можно как-нибудь в этом варианте сделать(написать) исключение для 1-2-ух определенных столбцов в таблице и оставить их без защиты?
А просто не выделять эти столбцы перед вызовом макроса защиты - для Вас не вариант?
Тогда, извините, это не ко мне, т.к. я всегда пишу УНИВЕРСАЛЬНЫЕ макросы, не привязанные к именам книг, листов, конкретным диапазонам, столбцам, строкам, ячейкам Универсальный инструмент - это для всех, а специальный делается по заказу. По заказам же я практически никогда не пишу.Alex_ST
А просто не выделять эти столбцы перед вызовом макроса защиты - для Вас не вариант?
Тогда, извините, это не ко мне, т.к. я всегда пишу УНИВЕРСАЛЬНЫЕ макросы, не привязанные к именам книг, листов, конкретным диапазонам, столбцам, строкам, ячейкам Универсальный инструмент - это для всех, а специальный делается по заказу. По заказам же я практически никогда не пишу.
Не выделять столбцы перед запуском макроса еще какой для меня вариант! Откуда ж я мог знать про вариант "невыделения": я же писал, что не сильно искушенный юзер - защита эта нужна как раз от меня и мне подобных. А про макросы только вчера начал изучать информацию как и что делать и куда идти.... Спасибо громадное за уточнение! Уже все протестировал - работает именно так как надо! P.S. А с первым вариантом пока не смог разобраться: перебрал все варианты и ничего не получается.....
А просто не выделять эти столбцы перед вызовом макроса защиты - для Вас не вариант?
Тогда, извините, это не ко мне, т.к. я всегда пишу УНИВЕРСАЛЬНЫЕ макросы, не привязанные к именам книг, листов, конкретным диапазонам, столбцам, строкам, ячейкам Универсальный инструмент - это для всех, а специальный делается по заказу. По заказам же я практически никогда не пишу.
Не выделять столбцы перед запуском макроса еще какой для меня вариант! Откуда ж я мог знать про вариант "невыделения": я же писал, что не сильно искушенный юзер - защита эта нужна как раз от меня и мне подобных. А про макросы только вчера начал изучать информацию как и что делать и куда идти.... Спасибо громадное за уточнение! Уже все протестировал - работает именно так как надо! P.S. А с первым вариантом пока не смог разобраться: перебрал все варианты и ничего не получается..... Serge1400
Alex_ST, Я про тот в котором F5 надо жать и потом выбирать: все варианты перебрал и ничего. Я же говорю, что я чайник. Так что редактирование защищенных ячеек для меня пока вопрос открытый
Alex_ST, Я про тот в котором F5 надо жать и потом выбирать: все варианты перебрал и ничего. Я же говорю, что я чайник. Так что редактирование защищенных ячеек для меня пока вопрос открытыйSerge1400
Сообщение отредактировал Serge1400 - Суббота, 02.07.2016, 22:24
Первый раз в жизни слышу про то, что имеются варианты нажатия клавиши F5 Поверьте, каким бы пальцем , другой частью тела либо предметом Вы не нажали клавишу F5 на клавиатуре, когда активно окно Excel, реакция компьютера будет одинаковая Она у Вас вообще есть на клавиатуре? Ну так откройте любую книгу Excel и нажмите её. Далее я уже описывал Выбираете то, что Вы хотите выделить (например, "Проверка данных"), потом нажимаете ОК. Если в выделенном диапазоне есть ячейки с проверкой данных, то они выделятся. Если таких ячеек нет, Excel ругнётся. А как Вашей версии Excel'я добраться до пункта "Проверка данных" я, извините, не могу знать. У меня, в 2003-ем это "Данные" → "Проверка... " Откроется окно проверки вводимых значений В нём выбираете в выпадающем списке "Тип данных": любое значение Жмёте ОК и все Ваши проверки данных, заданные в выделенных ячейках, удаляются. К стати, только сейчас заметил, что там есть ещё и кнопочка "Очистить все". Значит, ещё проще - тип данных можно не выбирать.
Первый раз в жизни слышу про то, что имеются варианты нажатия клавиши F5 Поверьте, каким бы пальцем , другой частью тела либо предметом Вы не нажали клавишу F5 на клавиатуре, когда активно окно Excel, реакция компьютера будет одинаковая Она у Вас вообще есть на клавиатуре? Ну так откройте любую книгу Excel и нажмите её. Далее я уже описывал Выбираете то, что Вы хотите выделить (например, "Проверка данных"), потом нажимаете ОК. Если в выделенном диапазоне есть ячейки с проверкой данных, то они выделятся. Если таких ячеек нет, Excel ругнётся. А как Вашей версии Excel'я добраться до пункта "Проверка данных" я, извините, не могу знать. У меня, в 2003-ем это "Данные" → "Проверка... " Откроется окно проверки вводимых значений В нём выбираете в выпадающем списке "Тип данных": любое значение Жмёте ОК и все Ваши проверки данных, заданные в выделенных ячейках, удаляются. К стати, только сейчас заметил, что там есть ещё и кнопочка "Очистить все". Значит, ещё проще - тип данных можно не выбирать.Alex_ST
Alex_ST, В моем случае (exel10)для снятия вашей защиты в преварительно выделенных ячейках, оказалось достаточно, зайти в "данные", выбрать "проверка данных" и в появившемся окошке нажать "очистить все". После этого любая из выделенных ячеек прекрасно редактируется. Лезешь в другие "неочищенные" ячейки - защита ругается! Так что все очень даже расчудесно получается и я вооружен! Еще раз спасибо за помощь и разъяснения! Самостоятельно я бы до этого никогда бы не допер.
Alex_ST, В моем случае (exel10)для снятия вашей защиты в преварительно выделенных ячейках, оказалось достаточно, зайти в "данные", выбрать "проверка данных" и в появившемся окошке нажать "очистить все". После этого любая из выделенных ячеек прекрасно редактируется. Лезешь в другие "неочищенные" ячейки - защита ругается! Так что все очень даже расчудесно получается и я вооружен! Еще раз спасибо за помощь и разъяснения! Самостоятельно я бы до этого никогда бы не допер. Serge1400
"Данные" → "Проверка... " … Откроется окно проверки вводимых значений В нём выбираете в выпадающем списке "Тип данных": любое значение Жмёте ОК и все Ваши проверки данных, заданные в выделенных ячейках, удаляются. … там есть ещё и кнопочка "Очистить все"
"Данные" → "Проверка... " … Откроется окно проверки вводимых значений В нём выбираете в выпадающем списке "Тип данных": любое значение Жмёте ОК и все Ваши проверки данных, заданные в выделенных ячейках, удаляются. … там есть ещё и кнопочка "Очистить все"
Alex_ST, Ну да - тоже самое написано. Делайте скидку на мою бестолковость в плане работы с Exel. После того, как мне удалось записать несложный макрос для снятия с ячейки или группы ячеек этой защиты (спасибо за вышеупомянутые подсказки), получился полноценный и очень функциональный вариант против косоруких "дураков". Но при этом, таблицу без проблем можно растаскивать, удалять строчки, столбцы и т.д. А при необходимости за секунду снять макросом с нужной ячейки защиту и редактировать содержимое. Как нынче говорят - большущий респект Alex_ST,! Очень нарядная и полезная штука получилась в итоге по моему.
Alex_ST, Ну да - тоже самое написано. Делайте скидку на мою бестолковость в плане работы с Exel. После того, как мне удалось записать несложный макрос для снятия с ячейки или группы ячеек этой защиты (спасибо за вышеупомянутые подсказки), получился полноценный и очень функциональный вариант против косоруких "дураков". Но при этом, таблицу без проблем можно растаскивать, удалять строчки, столбцы и т.д. А при необходимости за секунду снять макросом с нужной ячейки защиту и редактировать содержимое. Как нынче говорят - большущий респект Alex_ST,! Очень нарядная и полезная штука получилась в итоге по моему.Serge1400
Сообщение отредактировал Serge1400 - Воскресенье, 03.07.2016, 18:26
Уважаемый Alex-ST! Еще раз спасибо за этот замечательный макрос. На роботе уже неоднократно успели убедиться в его эффективности - пару раз спасал от возможных косяков в расчетах от невниматльных и криворуких. Я извиняюсь за возможную назойливость, но решился еще раз вас побеспокоить. Как писал выше, написал макрос рекордедером для снятия вашей защиты, но как он он кривовато работает - по ощущениям через раз. Выглядит он вот так
[vba]
Код
Sub Макрос1() ' ' Макрос1 Макрос ' доступ к редактированию ' ' Сочетание клавиш: Ctrl+я ' With Selection.Validation .Delete .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "ОШИБКА!" .InputMessage = "" .ErrorMessage = "В ячейке формула!" & Chr(13) & "" & Chr(10) & "Ввод данных запрещён!" .ShowInput = True .ShowError = True End With End Sub
[/vba]
Может в нем все таки что-то неправильно записалось? С уважением, Сергей
Уважаемый Alex-ST! Еще раз спасибо за этот замечательный макрос. На роботе уже неоднократно успели убедиться в его эффективности - пару раз спасал от возможных косяков в расчетах от невниматльных и криворуких. Я извиняюсь за возможную назойливость, но решился еще раз вас побеспокоить. Как писал выше, написал макрос рекордедером для снятия вашей защиты, но как он он кривовато работает - по ощущениям через раз. Выглядит он вот так
[vba]
Код
Sub Макрос1() ' ' Макрос1 Макрос ' доступ к редактированию ' ' Сочетание клавиш: Ctrl+я ' With Selection.Validation .Delete .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "ОШИБКА!" .InputMessage = "" .ErrorMessage = "В ячейке формула!" & Chr(13) & "" & Chr(10) & "Ввод данных запрещён!" .ShowInput = True .ShowError = True End With End Sub
[/vba]
Может в нем все таки что-то неправильно записалось? С уважением, СергейSerge1400
Сообщение отредактировал _Boroda_ - Среда, 13.07.2016, 11:08
[/vba] А то Вы сначала снимаете проверку, а потом её опять ставите Когда мой макрос ставит защиту, то он специально сначала удаляет ту проверку, которая могла быть прописана в ячейках, а потом уже ставит "защитную" проверку. Странно, что у Вас хоть через раз, но всё-таки снимает защиту. По идее вообще не должно.
Надо ограничиться одним[vba]
Код
Selection.Validation.Delete
[/vba] А то Вы сначала снимаете проверку, а потом её опять ставите Когда мой макрос ставит защиту, то он специально сначала удаляет ту проверку, которая могла быть прописана в ячейках, а потом уже ставит "защитную" проверку. Странно, что у Вас хоть через раз, но всё-таки снимает защиту. По идее вообще не должно.Alex_ST
С уважением, Алексей MS Excel 2003 - the best!!!
Сообщение отредактировал Alex_ST - Четверг, 14.07.2016, 10:51
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
А потом Ай-Ти-шники уже сделали импорт из базы данных в такие шаблоны. Так что переделывать не имеет смысла.
