Данные в Excel можно защищать от постороннего вмешательства. Это важно, потому что иногда вы тратите много времени и сил на создание сводной таблицы или объемного массива, а другой человек случайно или намеренно изменяет либо вовсе удаляет все ваши труды.
Рассмотрим способы защиты документа Excel и его отдельных элементов.
Защита ячейки Excel от изменения
Как поставить защиту на ячейку в Excel? По умолчанию все ячейки в Excel защищаемые. Это легко проверить: кликаем на любую ячейку правой кнопкой, выбираем ФОРМАТ ЯЧЕЕК – ЗАЩИТА. Видим, что галочка на пункте ЗАЩИЩАЕМАЯ ЯЧЕЙКА проставлена. Но это еще не значит, что они уже защищены от изменений.
Зачем нам эта информация? Дело в том, что в Excel нет такой функции, которая позволяет защитить отдельную ячейку. Можно выбрать защиту листа, и тогда все ячейки на нем будут защищены от редактирования и другого вмешательства. С одной стороны это удобно, но что делать, если нам нужно защитить не все ячейки, а лишь некоторые?
Рассмотрим пример. Имеем простую таблицу с данными. Такую таблицу нам нужно разослать в филиалы, чтобы магазины заполнили столбец ПРОДАННОЕ КОЛИЧЕСТВО и отправили обратно. Во избежание внесения каких-то изменений в другие ячейки, защитим их.
Для начала освободим от защиты те ячейки, куда сотрудники филиалов будут вносить изменения. Выделяем D4:D11, правой кнопкой вызываем меню, выбираем ФОРМАТ ЯЧЕЕК и убираем галочку с пункта ЗАЩИЩАЕМАЯ ЯЧЕЙКА.
Теперь выбираем вкладку РЕЦЕНЗИРОВАНИЕ – ЗАЩИТИТЬ ЛИСТ. Появляется такое окно, где будут проставлены 2 галочки. Первую из них мы убираем, чтобы исключить любое вмешательство сотрудников филиалов, кроме заполнения столбца ПРОДАННОЕ КОЛИЧЕСТВО. Придумываем пароль и нажимаем ОК.
Внимание! Не забудьте свой пароль!
Теперь в диапазон D4:D11 посторонние лица смогут только вписать какое-то значение. Т.к. мы ограничили все остальные действия, никто не сможет даже изменить цвет фона. Все средства форматирования на верхней панели инструментов не активные. Т.е. они не работают.
Защита книги Excel от редактирования
Если на одном компьютере работает несколько человек, то целесообразно защищать свои документы от редактирования третьими лицами. Можно ставить защиту не только на отдельные листы, но и на всю книгу.
Когда книга будет защищена, посторонние смогут открывать документ, видеть написанные данные, но переименовать листы, вставить новый, поменять их расположение и т.п. Попробуем.
Прежнее форматирование сохраняем. Т.е. у нас по-прежнему можно вносить изменения только в столбец ПРОДАННОЕ КОЛИЧЕСТВО. Чтобы защитить книгу полностью, на вкладке РЕЦЕНЗИРОВАНИЕ выбираем ЗАЩИТИТЬ КНИГУ. Оставляем галочки напротив пункта СТРУКТУРУ и придумываем пароль.
Теперь, если мы попробуем переименовать лист, у нас это не получится. Все команды серого цвета: они не работают.
Снимается защита с листа и книги теми же кнопками. При снятии система будет требовать тот же пароль.
Хотя в Excel реализована общая защита рабочих книг и листов, этот грубый инструмент не может предоставить пользователям ограниченные привилегии — если только вы не примените какие-то трюки. [1] Управлять действиями пользователей можно путем ответов на события. События — это действия, которые происходят по мере того, как вы работаете с книгами и листами. Наиболее часто происходящие события — это открытие рабочей книги, ее сохранение и закрытие после завершения работы. Вы можете заставить Excel автоматически выполнять некоторый код Visual Basic в момент, когда происходит одно из этих событий.
Отключение в рабочей книге команды Сохранить
Можно сделать так, чтобы рабочую книгу нельзя было сохранить, и она открывалась в режиме «только для чтения». Для этого в вашей книге выполните команду Сохранить как и в окне Сохранение документа кликните на стрелку вниз на кнопке Сервис (рис. 1). Выберите опцию Общие параметры.
Рис. 1. Доступ к меню Общие параметры при сохранении книги Excel
Скачать заметку в формате Word или pdf, примеры в формате Excel (файл содержит макросы)
В открывшемся окне Общие параметры (рис. 2) задайте Пароль для изменения и кликните на Рекомендовать доступ только для чтения. Кликните Ok, повторите ввод пароля, и сохраните книгу. Если появится окно Книга с таким именем уже существует, заменить ее? Кликните Да.
Рис. 2. Задание пароля для изменения книги в окне Общие параметры
Отключение в рабочей книге команды Сохранить как
В предыдущем разделе вы научились не давать возможность пользователям сохранить книгу поверх вашей, но хитрые пользователи могут сохранить копию книги в другом месте. 🙂 Чтобы отключить эту возможность, потребуется код VBA.
Событие Before Save, которое вы будете использовать в следующем коде, было впервые создано в Excel 97. Как предполагает его имя, это событие происходит перед сохранением рабочей книги, позволяя вам поймать действия пользователя еще перед этим фактом, выдать предупреждение и запретить Excel сохранять файл.
Перед тем как пытаться выполнить следующий трюк, обязательно сначала сохраните рабочую книгу в формате с поддержкой макросов *.xlsm. Если вы напишете этот код до того, как сохраните книгу, то не сможете более сохранять изменения.
Чтобы написать код, откройте рабочую книгу и пройдите по меню Разработчик –> Visual Basic. [2] В окне Microsoft Visual Basic for Applications перейдите в окно Project – VBAProject. Если нужно раскройте строку VBAProject (Защита книги Excel от копирования и печати. Примеры) и правой кнопкой мыши щелкните на значке ЭтаКнига. Выберите опцию View Code (рис. 3). Откроется окно Защита книги Excel от копирования и печати. Примеры.xlsx — Эта книга (Code). Это окно частного модуля для объекта ЭтаКнига. Это важно, и вы не добьетесь цели, если запишите код в обычном модуле.
Рис. 3. Открыто окно частного модуля для объекта ЭтаКнига
Введите следующий код (рис. 4) и нажмите сочетание клавиш Alt+Q, чтобы вернуться в Excel.
Option Explicit
Private Sub workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim lReply As Long
If SaveAsUI = True Then
lReply = MsgBox( " Sorry, you are not allowed to save this workbook as another name. " _
& " Do you wish to save this workbook. " , vbQuestion + vbOKCancel)
Cancel = (lReply = vbCancel)
If Cancel = False Then Me.Save
Cancel = True
End If
End Sub
Рис. 4. Код в частном модуле ЭтаКнига (чтобы увеличить изображение, кликните на картинке правой кнопкой мыши и выберите опцию Открыть картинку в новой вкладке)
Проверьте, что получилось. Выберите команду Файл –> Сохранить как, вы увидите сообщение о том, что эту книгу запрещено сохранять под другим именем (рис. 5).
Рис. 5. Сообщение о запрете на сохранение книги под другим именем
Очень хитрые пользователи могут открыть редактор VBA, удалить код, и сохранить книгу в другом месте или с другим именем.
Запрет печати рабочей книги
Итак, вы запретили сохранение книги, копирование книги в другое место, но пользователи всё еще могут распечатать данные. При помощи события Excel Before Print можно отследить и предупредить действия пользователя. Как и ранее, откройте окно частного модуля для объекта ЭтаКнига и введите код:
Option Explicit
Private Sub workbook_BeforePrint(Cancel As Boolean)
Cancel = True
MsgBox " Выводить эту рабочую книгу на печать нельзя " , vbInformation
End Sub
Закончив ввод кода, нажмите сочетание клавиш Alt+Q, чтобы вернуться в Excel. Теперь каждый раз, когда пользователи будут пытаться напечатать эту рабочую книгу, ничего не случится. Строка сообщения MsgBox не обязательна, но всегда полезно включать ее, хотя бы для того, чтобы проинформировать пользователя.
Если вы хотите, чтобы пользователи не могли печатать только определенные листы в книге, вместо предыдущего кода используйте следующий:
Option Explicit
Private Sub workbook_BeforePrint(Cancel As Boolean)
Select Case ActiveSheet.Name
Case " Sheet1 " , " Sheet2 "
Cancel = True
MsgBox " Выводить этот рабочий лист на печать нельзя " , vbInformation
End Select
End Sub
Обратите внимание, что печать будет остановлена только для листов Sheet1 и Sheet2. Конечно, имена листов вашей рабочей книги могут быть любыми. Чтобы добавить их в список кода, запишите их в кавычках, разделяя запятыми. Если вы хотите запретить печать только одного листа, укажите только одно название в кавычках, не ставя запятую.
У хитрых пользователей всё еще останется возможность сделать скриншот экрана с вашей засекреченной книгой.
К сожалению, пользователи могут обойти эту защиту и иным путем. Им достаточно полностью отключить макросы. Для этого нужно пройти по меню Файл –> Параметры –> Центр управления безопасностью, и кликнуть на кнопке Параметры центра управления безопасностью. В открывшемся окне перейти на складку Параметры макросов и поставить переключатель в позицию Отключить все макросы без уведомления. С другой стороны, если в электронной таблице будут находиться и полезные для них макросы, пользователи, вероятно, включат использование макросов. Описанные трюки представляют собой просто удобную возможность и не обеспечивают мощной защиты данных.
[1] По материалам книги Д.Холи, Р.Холи. Excel 2007. Трюки, стр. 28–32
[2] Если вкладка Разработчик отсутствует, пройдите по меню Файл –> Параметры Excel –> Настроить ленту. Поставьте галочку напротив вкладки Разработчик.
Можно ли запретить в экселе копирование листа(или его части) документа — куда либо?
Сформированный в экселе документ нужно без изменения распечатать один раз, при этом запретив его исправлять, и копировать его фрагменты куда либо. Подскажите пожалуйста — возможно ли это?
← →
Alexandr ( 2003-03-17 10:10 ) [1]
можно в сторону макросов покопать, но не факт.
Да и на Экселе свет клином не сошелся.
← →
Sheriff ( 2003-03-17 10:16 ) [2]
Excel (ХР) дает следующий макрос
ActiveWorkbook.Protect Structure:=True, Windows:=True
← →
Veronika ( 2003-03-17 10:20 ) [3]
Не хочется менять способ построения отчётов.
Поискала в справке — ничего похожего на BeforeCopyToClipBoard нету.
← →
Veronika ( 2003-03-17 10:41 ) [4]
Этот кусочек кода не запрещает выделить на листе фрагмент и вставить его в другой документ.
← →
Sheriff ( 2003-03-17 11:04 ) [5]
скрывайте лист
ActiveWindow.SelectedSheets.Visible = False
а не поясните подробно мотивы таких издевательств над Excel?
← →
Veronika ( 2003-03-17 11:17 ) [6]
Просто хотелось бы, чтобы этот документ, назовём его "накладная" был напечатан действительно только один раз, и не перепечатывался, и чтобы все исправления — только через клиентское приложение.
← →
Alexandr ( 2003-03-17 11:24 ) [7]
для этого уходят от excel
← →
Roki ( 2003-03-17 11:33 ) [8]
← →
Veronika ( 2003-03-17 11:33 ) [9]
Т.е. вердикт таков — что НЕЛЬЗЯ что ли?
← →
fareader ( 2003-03-17 11:34 ) [10]
Хммм. интересный подход — печатать прогораммно накладные через ексель — меня пользователи бы уже живьем сожрали за такое. Тем более, что это не отчет для менеджера, которому информация должна быть дана в виде екслевской таблицы для последующего анализа.
А не проще ли использовать что-то более адаптированное к таим вещам:
FastReport, FreeReport, QuickReport и много чего еще, кстати многие компоненты поддерживают експорт в ексель.
← →
Sheriff ( 2003-03-17 11:36 ) [11]
да не показывайте пользователю вообще Excel.
сформировали накладную, отправили на печать и
тут же разрушили страницу (стерли данные и т.д.).
(все это не отображая Excel)
и в чем тут проблема?
← →
fareader ( 2003-03-17 11:36 ) [12]
2Veronika (17.03.03 11:33)
рекомендую рассмотреть предложение Sheriff (17.03.03 11:04) о снятии визибл с листа.
← →
Veronika ( 2003-03-17 11:38 ) [13]
хм. а какая разница — через чё её печатать? Что любой другой отчёт нельзя подделать что ли?
← →
Alexandr ( 2003-03-17 11:39 ) [14]
> Хммм. интересный подход — печатать прогораммно накладные
> через ексель — меня пользователи бы уже живьем сожрали за
> такое.
а меня бы пользователи живьем сожрали, если бы я не дал им возможность вручную редактировать некорые документы прямо в word или excel :))))))))))))))
← →
Fareader ( 2003-03-17 11:45 ) [15]
>а меня бы пользователи живьем сожрали, если бы я не дал им >возможность вручную редактировать некорые документы прямо в ?>word или excel :))))))))))))))
У всех своя специфика 🙂
← →
Alexandr ( 2003-03-17 11:49 ) [16]
здесь палка о двух концах.
И у того и у другого есть свои достоинства и недостатки.
Но совместить 2 в 1 не так просто.