Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Защита ячеек с формулами, но работоспособностью макросов - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Защита ячеек с формулами, но работоспособностью макросов (Макросы/Sub)
Защита ячеек с формулами, но работоспособностью макросов
Account196 Дата: Пятница, 14.09.2018, 11:16 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Здравствуйте! Позавчера мне светлые и умные головы этого форума очень хорошо помогли с решением проблемы в двух вопросах.
Следуя их инструкциям и указаниям, мною была доведена таблица до удовлетворяющего состояния. Теперь возник еще один (самый главный вопрос) - Можно ли защитить ячейки, и скрыть в них формулы, чтобы пользователи (а их кроме меня будет еще 2 человека), не внесли изменения в структуру таблиц, но при этом сохранить работоспособность макросов на добавление показателей из справочников.
Пользователю доступны для редактирования только ячейки со светло-синим фоном и выбор из справочников (светло-зеленый фон), все белые ячейки - недоступны. Если делать защиту листа с вводом пароля, тогда нарушается работоспособность макроса на добавление строк в таблицу. Удалось добиться скрытия формул и защиты ячеек и при этом работоспособности макроса только на вкладке "Производственные затраты и ФР" путем помещения следующего кода перед началом макроса:
[vba]
Код
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowFiltering:=True, UserInterfaceOnly:=True, Password:="1111"
[/vba]
, а на вкладке "Реализация и себестоимость" и "Сводный отчет" - нет.
Если кто-то сталкивался с этим вопросом, или есть готовое решение, помогите, пожалуйста. Спасибо.
К сообщению приложен файл: 4074227.7z(96.4 Kb)


Сообщение отредактировал Account196 - Пятница, 14.09.2018, 11:17
 
Ответить
СообщениеЗдравствуйте! Позавчера мне светлые и умные головы этого форума очень хорошо помогли с решением проблемы в двух вопросах.
Следуя их инструкциям и указаниям, мною была доведена таблица до удовлетворяющего состояния. Теперь возник еще один (самый главный вопрос) - Можно ли защитить ячейки, и скрыть в них формулы, чтобы пользователи (а их кроме меня будет еще 2 человека), не внесли изменения в структуру таблиц, но при этом сохранить работоспособность макросов на добавление показателей из справочников.
Пользователю доступны для редактирования только ячейки со светло-синим фоном и выбор из справочников (светло-зеленый фон), все белые ячейки - недоступны. Если делать защиту листа с вводом пароля, тогда нарушается работоспособность макроса на добавление строк в таблицу. Удалось добиться скрытия формул и защиты ячеек и при этом работоспособности макроса только на вкладке "Производственные затраты и ФР" путем помещения следующего кода перед началом макроса:
[vba]
Код
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowFiltering:=True, UserInterfaceOnly:=True, Password:="1111"
[/vba]
, а на вкладке "Реализация и себестоимость" и "Сводный отчет" - нет.
Если кто-то сталкивался с этим вопросом, или есть готовое решение, помогите, пожалуйста. Спасибо.

Автор - Account196
Дата добавления - 14.09.2018 в 11:16
Kuzmich Дата: Пятница, 14.09.2018, 11:29 | Сообщение № 2
Группа: Проверенные
Ранг: Обитатель
Сообщений: 380
Репутация: 75 ±
Замечаний: 0% ±

Excel 2003
Не защищая лист и используя все возможности Excel.
Выделить ячейки с формулами: Правка-Перейти-выделить поставить точку у формул
Данные-Проверка-Параметры: тип данных: другой, Формула:=""
 
Ответить
СообщениеНе защищая лист и используя все возможности Excel.
Выделить ячейки с формулами: Правка-Перейти-выделить поставить точку у формул
Данные-Проверка-Параметры: тип данных: другой, Формула:=""

Автор - Kuzmich
Дата добавления - 14.09.2018 в 11:29
StoTisteg Дата: Пятница, 14.09.2018, 12:35 | Сообщение № 3
Группа: Авторы
Ранг: Старожил
Сообщений: 1055
Репутация: 89 ±
Замечаний: 0% ±

Excel 2010
Как вариант — просто в событии Worksheet_Change анализируем Target и если он белый, то Application.Undo. А в начале макроса отключаем события (и не забываем включить их в конце).


Интуитивно понятный код - это когда интуитивно понятно, что это код.
 
Ответить
СообщениеКак вариант — просто в событии Worksheet_Change анализируем Target и если он белый, то Application.Undo. А в начале макроса отключаем события (и не забываем включить их в конце).

Автор - StoTisteg
Дата добавления - 14.09.2018 в 12:35
Account196 Дата: Пятница, 14.09.2018, 12:49 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Не защищая лист и используя все возможности Excel.
Выделить ячейки с формулами: Правка-Перейти-выделить поставить точку у формул
Данные-Проверка-Параметры: тип данных: другой, Формула:=""

Спасибо за подсказку, но вообще ничего не происходит. Формулы как показывались, так и остаются. Ячейки для редактирования доступны. И при повторном входе в ячейки с формулами по пути Данные->Проверка->Параметры->Формула, в поле значения ="" нет. Оно остается пустым.
 
Ответить
Сообщение
Не защищая лист и используя все возможности Excel.
Выделить ячейки с формулами: Правка-Перейти-выделить поставить точку у формул
Данные-Проверка-Параметры: тип данных: другой, Формула:=""

Спасибо за подсказку, но вообще ничего не происходит. Формулы как показывались, так и остаются. Ячейки для редактирования доступны. И при повторном входе в ячейки с формулами по пути Данные->Проверка->Параметры->Формула, в поле значения ="" нет. Оно остается пустым.

Автор - Account196
Дата добавления - 14.09.2018 в 12:49
_Boroda_ Дата: Пятница, 14.09.2018, 13:18 | Сообщение № 5
Группа: Модераторы
Ранг: Местный житель
Сообщений: 13515
Репутация: 5530 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
В модуль книги напишите вот так
[vba]
Код
Private Sub Workbook_Open()
    For Each Sh In Me.Worksheets
        Sh.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
            , AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowFiltering:=True, UserInterfaceOnly:=True, Password:="1111"
    Next Sh
    Sheets("Главная форма").Select
End Sub
[/vba]
К сообщению приложен файл: ____11.xlsb(86.5 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеВ модуль книги напишите вот так
[vba]
Код
Private Sub Workbook_Open()
    For Each Sh In Me.Worksheets
        Sh.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
            , AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowFiltering:=True, UserInterfaceOnly:=True, Password:="1111"
    Next Sh
    Sheets("Главная форма").Select
End Sub
[/vba]

Автор - _Boroda_
Дата добавления - 14.09.2018 в 13:18
Account196 Дата: Пятница, 14.09.2018, 22:05 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
В модуль книги напишите вот так

Спасибо. Написал, проверил. Изменений нет. Добавление строк на вкладке "Реализация и себестоимость" не производится. Как были формулы открыты, так и остались, но после снятия пароля (1111), добавление возможно.
В модуль книги, как Вы и сказали, прописал.
К сообщению приложен файл: 2846511.png(92.7 Kb)
 
Ответить
Сообщение
В модуль книги напишите вот так

Спасибо. Написал, проверил. Изменений нет. Добавление строк на вкладке "Реализация и себестоимость" не производится. Как были формулы открыты, так и остались, но после снятия пароля (1111), добавление возможно.
В модуль книги, как Вы и сказали, прописал.

Автор - Account196
Дата добавления - 14.09.2018 в 22:05
Kuzmich Дата: Пятница, 14.09.2018, 22:19 | Сообщение № 7
Группа: Проверенные
Ранг: Обитатель
Сообщений: 380
Репутация: 75 ±
Замечаний: 0% ±

Excel 2003
Цитата
но вообще ничего не происходит. Формулы как показывались, так и остаются

А вы пробовали что-то внести в эти ячейки?
 
Ответить
Сообщение
Цитата
но вообще ничего не происходит. Формулы как показывались, так и остаются

А вы пробовали что-то внести в эти ячейки?

Автор - Kuzmich
Дата добавления - 14.09.2018 в 22:19
_Boroda_ Дата: Пятница, 14.09.2018, 22:25 | Сообщение № 8
Группа: Модераторы
Ранг: Местный житель
Сообщений: 13515
Репутация: 5530 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Чуть переписал макрос
А по поводу невидимости формул - я как-то надеялся, что Вы сможете самостоятельно зайти в защищенные ячейки Формат - Защита и поставить галку Скрыть формулы
К сообщению приложен файл: _112.xlsb(91.3 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЧуть переписал макрос
А по поводу невидимости формул - я как-то надеялся, что Вы сможете самостоятельно зайти в защищенные ячейки Формат - Защита и поставить галку Скрыть формулы

Автор - _Boroda_
Дата добавления - 14.09.2018 в 22:25
Account196 Дата: Пятница, 14.09.2018, 23:01 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
А по поводу невидимости формул - я как-то надеялся, что Вы сможете самостоятельно зайти в защищенные ячейки Формат - Защита и поставить галку Скрыть формулы

Пробовал, Александр. Заходил, ставил. Ctrl+G -> Выделить (.) формулы -> Ок. Далее Правой кнопкой мыши -> Формат ячеек -> Защита [v] Скрыть формулы -> Ок.
Мистика какая-то. Теперь добавление строк при нажатии кнопки "Добавить позицию в отчет" на вкладке "реализация и себестоимость" возможно только одной (первой), а сами формулы по адресу, например, B10, D10,E10, K10,L10,M10 стираются и заменяются на #Н/Д (нет данных)...
Допуска на добавленные ячейки C10,F10,G10,H10,I8,J8,I10,J10 (предназначенных для ввода информации пользователями) нет.
К сообщению приложен файл: 4277070.png(83.4 Kb)
 
Ответить
Сообщение
А по поводу невидимости формул - я как-то надеялся, что Вы сможете самостоятельно зайти в защищенные ячейки Формат - Защита и поставить галку Скрыть формулы

Пробовал, Александр. Заходил, ставил. Ctrl+G -> Выделить (.) формулы -> Ок. Далее Правой кнопкой мыши -> Формат ячеек -> Защита [v] Скрыть формулы -> Ок.
Мистика какая-то. Теперь добавление строк при нажатии кнопки "Добавить позицию в отчет" на вкладке "реализация и себестоимость" возможно только одной (первой), а сами формулы по адресу, например, B10, D10,E10, K10,L10,M10 стираются и заменяются на #Н/Д (нет данных)...
Допуска на добавленные ячейки C10,F10,G10,H10,I8,J8,I10,J10 (предназначенных для ввода информации пользователями) нет.

Автор - Account196
Дата добавления - 14.09.2018 в 23:01
Account196 Дата: Пятница, 14.09.2018, 23:02 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
А вы пробовали что-то внести в эти ячейки?

Да, всяко-разно пробовал.... Никак... Как стопор какой-то...
 
Ответить
Сообщение
А вы пробовали что-то внести в эти ячейки?

Да, всяко-разно пробовал.... Никак... Как стопор какой-то...

Автор - Account196
Дата добавления - 14.09.2018 в 23:02
Pelena Дата: Пятница, 14.09.2018, 23:09 | Сообщение № 11
Группа: Админы
Ранг: Местный житель
Сообщений: 13225
Репутация: 2909 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
Account196, а зачем у Вас в модуле книги два макроса с именем Workbook_Open?
Непорядок. Объедините в один


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеAccount196, а зачем у Вас в модуле книги два макроса с именем Workbook_Open?
Непорядок. Объедините в один

Автор - Pelena
Дата добавления - 14.09.2018 в 23:09
Account196 Дата: Пятница, 14.09.2018, 23:21 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
а зачем у Вас в модуле книги два макроса Workbook_Open?

Да, кстати, исправил, но после того, как поместил на форум картинку. Закомментировал. А как я понял уже давно, комментарии в макросах не выполняются. В оригинале убрал первый Workbook_Open


Сообщение отредактировал Account196 - Пятница, 14.09.2018, 23:29
 
Ответить
Сообщение
а зачем у Вас в модуле книги два макроса Workbook_Open?

Да, кстати, исправил, но после того, как поместил на форум картинку. Закомментировал. А как я понял уже давно, комментарии в макросах не выполняются. В оригинале убрал первый Workbook_Open

Автор - Account196
Дата добавления - 14.09.2018 в 23:21
_Boroda_ Дата: Воскресенье, 16.09.2018, 15:33 | Сообщение № 13
Группа: Модераторы
Ранг: Местный житель
Сообщений: 13515
Репутация: 5530 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Вы файл-то покажете?


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеВы файл-то покажете?

Автор - _Boroda_
Дата добавления - 16.09.2018 в 15:33
Alex_ST Дата: Понедельник, 17.09.2018, 22:07 | Сообщение № 14
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3061
Репутация: 568 ±
Замечаний: 0% ±

2003
Из моих старых запасов:[vba]
Код
Sub Formula_Protect_with_CellValidation()
   If ActiveWindow.RangeSelection.SpecialCells(xlCellTypeFormulas) Is Nothing Then Exit Sub
   ActiveWindow.RangeSelection.SpecialCells(xlCellTypeFormulas).Select
   With Selection.Validation
      .Delete
      .Add Type:=xlValidateCustom, Formula1:="="""""
      .ErrorTitle = "В ячейке формула!": .ErrorMessage = "Ввод данных запрещён" & vbCrLf & "Нажмите ""ОТМЕНА"""
      .ShowError = True
   End With
End Sub
[/vba]Защищает от изменения ячейки с формулами в выделенном диапазоне.



С уважением,
Алексей
MS Excel 2003 - the best!!!
 
Ответить
СообщениеИз моих старых запасов:[vba]
Код
Sub Formula_Protect_with_CellValidation()
   If ActiveWindow.RangeSelection.SpecialCells(xlCellTypeFormulas) Is Nothing Then Exit Sub
   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
Дата добавления - 17.09.2018 в 22:07
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Защита ячеек с формулами, но работоспособностью макросов (Макросы/Sub)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс цитирования
© 2010-2018 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!