Здравствуйте, форумчане! Подскажите - как защитить ячейки от изменения или удаления так, чтобы макрос работал? Один макрос - вставляет строку в нужном диапазоне и копирует туда формулы со строки выше. Пользователь может заполнять ячейки залитые серым (B14:D37, N14:N37 и еще немножко в шапке листа). Второй макрос - удаляет последнюю заполненную строку. Если делать через стандартную защиту ячеек листа - макросы перестают работать. Если делать через проверку данных - пользователь не может изменить формулы, но может их удалить. Как защитить формулы от юзеров чтобы и макросы остались рабочими? Или как разрешить макросу копировать защищенные ячейки?
Здравствуйте, форумчане! Подскажите - как защитить ячейки от изменения или удаления так, чтобы макрос работал? Один макрос - вставляет строку в нужном диапазоне и копирует туда формулы со строки выше. Пользователь может заполнять ячейки залитые серым (B14:D37, N14:N37 и еще немножко в шапке листа). Второй макрос - удаляет последнюю заполненную строку. Если делать через стандартную защиту ячеек листа - макросы перестают работать. Если делать через проверку данных - пользователь не может изменить формулы, но может их удалить. Как защитить формулы от юзеров чтобы и макросы остались рабочими? Или как разрешить макросу копировать защищенные ячейки?ttv
Sub Protect_for_User_Non_for_VBA() ActiveSheet.Protect Password:="1111", UserInterfaceOnly:=True End Sub
[/vba]
Теперь вылезла такая проблема: пользователю разрешено вставлять данные в определенные ячейки, все остальные заблокированы, пользователь даже не может их выделить. Но если пользователь вставляет свои данные способом "копировать-вставить" - то те ячейки, которые он только что заполнил сразу же становятся заблокированными. А если пользователь ошибся - он не сможет изменить то, что вставил только что. Заблокированы листы с применением пароля и пароль пользователь знать не будет. Как сделать так чтобы при любых способах вставки данных пользователя: 1) разрешенные к редактированию пользователем ячейки не блокировались; 2) сохранялось форматирование (в т.ч. УФ) в моем файле, а не то что пользователь откуда-то скопировал.
Sub Protect_for_User_Non_for_VBA() ActiveSheet.Protect Password:="1111", UserInterfaceOnly:=True End Sub
[/vba]
Теперь вылезла такая проблема: пользователю разрешено вставлять данные в определенные ячейки, все остальные заблокированы, пользователь даже не может их выделить. Но если пользователь вставляет свои данные способом "копировать-вставить" - то те ячейки, которые он только что заполнил сразу же становятся заблокированными. А если пользователь ошибся - он не сможет изменить то, что вставил только что. Заблокированы листы с применением пароля и пароль пользователь знать не будет. Как сделать так чтобы при любых способах вставки данных пользователя: 1) разрешенные к редактированию пользователем ячейки не блокировались; 2) сохранялось форматирование (в т.ч. УФ) в моем файле, а не то что пользователь откуда-то скопировал.ttv
ttv, попробуйте так: Это обработчик события изменения ячейки, код выполняется только когда включен режим копирования или вырезания. Код нужно добавить в модуль листа. [vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range)
With Application If .CutCopyMode = 0 Then Exit Sub .EnableEvents = 0 .Undo Target.PasteSpecial Paste:=xlPasteValues .EnableEvents = 1 End With 'Application
End Sub
[/vba]
ttv, попробуйте так: Это обработчик события изменения ячейки, код выполняется только когда включен режим копирования или вырезания. Код нужно добавить в модуль листа. [vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range)
With Application If .CutCopyMode = 0 Then Exit Sub .EnableEvents = 0 .Undo Target.PasteSpecial Paste:=xlPasteValues .EnableEvents = 1 End With 'Application
Получается, но только если защита на лист стоит без пароля. А когда защита с паролем - не работает. Что нужно добавить в макрос чтобы заработал и на запароленой защите листа? На другие макросы я пишу самой первой строчкой кода вот такое [vba]
Получается, но только если защита на лист стоит без пароля. А когда защита с паролем - не работает. Что нужно добавить в макрос чтобы заработал и на запароленой защите листа? На другие макросы я пишу самой первой строчкой кода вот такое [vba]
ttv, сложно так сказать, где проблема. Пришлите текущий файл. У меня все работает и под защитой с паролем, Вы сделали серые ячейки не защищаемыми? ПКМ -> формат ячеек -> защита -> снять галку "защищаемая ячейка"
На другие макросы я пишу самой первой строчкой кода вот такое
Этот код нужно поместить в событие Workbook_Open в модуль книги: [vba]
Код
Private Sub Workbook_Open() Sheets("Данные").Protect Password:="1111", UserInterfaceOnly:=True End Sub
[/vba] Из остальных макросов удалить.
ttv, сложно так сказать, где проблема. Пришлите текущий файл. У меня все работает и под защитой с паролем, Вы сделали серые ячейки не защищаемыми? ПКМ -> формат ячеек -> защита -> снять галку "защищаемая ячейка"
серые ячейки не защищены и должны такими остаться. В эти ячейки пользователь вводит свои данные. Проблема в том, что данные берутся из разных файлов, и как правило в эксель по умолчанию все ячейки в файле защищенные. Соответсвенно если пользователь копирует данные откуда-то, то и вставляет их в мою таблицу с этим же свойством защищенной ячейки. А мне надо чтобы макрос автоматически делал вставку только значений. В общем - если копировать данные и вставлять через "щелчок ПКМ в самой таблице - вставить" - то все нормально. А если у пользователя много разных данных - копируют кусками сразу несколько, потом открывают мой файл и открывают окно буфера обмена и вот там щелкают на скопированное чтобы вставить в таблицу - то тогда только что вставленные ячейки получаются заблокированы.
серые ячейки не защищены и должны такими остаться. В эти ячейки пользователь вводит свои данные. Проблема в том, что данные берутся из разных файлов, и как правило в эксель по умолчанию все ячейки в файле защищенные. Соответсвенно если пользователь копирует данные откуда-то, то и вставляет их в мою таблицу с этим же свойством защищенной ячейки. А мне надо чтобы макрос автоматически делал вставку только значений. В общем - если копировать данные и вставлять через "щелчок ПКМ в самой таблице - вставить" - то все нормально. А если у пользователя много разных данных - копируют кусками сразу несколько, потом открывают мой файл и открывают окно буфера обмена и вот там щелкают на скопированное чтобы вставить в таблицу - то тогда только что вставленные ячейки получаются заблокированы.
попробовала, у меня также - если вставлять тыкая мышью в окно буфера обмена - то вставляется заблокированным и с сохранением исходного форматирования (желтая заливка и т.д.).
А если вставлять просто ПКМ в таблицу - то все нормально.
попробовала, у меня также - если вставлять тыкая мышью в окно буфера обмена - то вставляется заблокированным и с сохранением исходного форматирования (желтая заливка и т.д.).
А если вставлять просто ПКМ в таблицу - то все нормально.ttv
Сорри, про буфер обмена проглядел. У меня нет опыта общения с ним, поэтому тут помочь не смогу. Судя по информации в гугле, буфер обмена это уже другая история, стандартными методами к нему не подобраться.
Как вариант, предлагаю такой подход: Если открыт буфер обмена, то на любые изменения ячеек запоминать их значения во временной переменной, отменять изменения, и вставлять туда же значения из переменной, если нет - отслеживать режим копирования/вырезания. [vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range)
With Application Call Module9.AppSettings(0) If .DisplayClipboardWindow Then Dim aTmp() As Variant aTmp = IIf(Target.Count > 1, Target.Value, Array(Target.Value)) .Undo: Target.Value = aTmp Else If .CutCopyMode > 0 Then .Undo: Target.PasteSpecial Paste:=xlPasteValues End If '.CutCopyMode End If '.DisplayClipboardWindow Call Module9.AppSettings(1) End With 'Application
End Sub
[/vba] При этом нужно обязательно добавить следующий код в модуль9: [vba]
Код
Public Sub AppSettings(ByVal bValue As Boolean) With Application: .ScreenUpdating = bValue: .EnableEvents = bValue: End With End Sub
[/vba] А в Ваши макросы добавить вызовы в начале и в конце: [vba]
[/vba] Это делается чтобы обработчик Worksheet_Change не срабатывал когда не надо. Добавил файл:
Сорри, про буфер обмена проглядел. У меня нет опыта общения с ним, поэтому тут помочь не смогу. Судя по информации в гугле, буфер обмена это уже другая история, стандартными методами к нему не подобраться.
Как вариант, предлагаю такой подход: Если открыт буфер обмена, то на любые изменения ячеек запоминать их значения во временной переменной, отменять изменения, и вставлять туда же значения из переменной, если нет - отслеживать режим копирования/вырезания. [vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range)
With Application Call Module9.AppSettings(0) If .DisplayClipboardWindow Then Dim aTmp() As Variant aTmp = IIf(Target.Count > 1, Target.Value, Array(Target.Value)) .Undo: Target.Value = aTmp Else If .CutCopyMode > 0 Then .Undo: Target.PasteSpecial Paste:=xlPasteValues End If '.CutCopyMode End If '.DisplayClipboardWindow Call Module9.AppSettings(1) End With 'Application
End Sub
[/vba] При этом нужно обязательно добавить следующий код в модуль9: [vba]
Код
Public Sub AppSettings(ByVal bValue As Boolean) With Application: .ScreenUpdating = bValue: .EnableEvents = bValue: End With End Sub
[/vba] А в Ваши макросы добавить вызовы в начале и в конце: [vba]
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
Пользователи рыдают от счастья 
