Защита ячеек в excel vba
В любом случае, этот макрос может запускаться автоматически при открытии файла или при нажатии любой ячейки. Я не хочу, чтобы людям приходилось запускать макрос вручную, чтобы защитить нужные диапазоны.
Кроме того, мне нужно будет защитить 12 несмежных диапазонов. Например: I11:I20 и K11:K20 и M11:20 и т.д. Как мне это сделать?
И последнее, и здесь может быть слишком много вопросов, но можно ли применить защиту к приведенным выше примерам диапазонов, НО затем распространить защиту на дополнительные строки по мере добавления новых? Другими словами, защита будет применяться к I11:I20 и K11:K20 и M11:20, но пользователь сможет добавить новую строку (строка 21) с новыми данными, но как только новая строка будет добавлена, тогда защита будет применяться к I11:I21 и K11:K21 и M11:21. Я прошу луну? :-)
Спасибо за все, что вы уже предоставили! Я не могу отблагодарить вас и других людей, подобных вам, за то, что вы поделились своими знаниями. Удивительно!
Дорогой Карлос,
Следующий код VBA может помочь вам решить проблему. Пожалуйста, заполните свои диапазоны в четвертой строке кода и нажмите клавиши Alt + Q, чтобы вернуться к рабочему листу. Затем перейдите на другой рабочий лист, а затем вернитесь к текущему листу, чтобы активировать код. Спасибо за ваш комментарий.
Dim xRg как диапазон
Частная подпрограмма Worksheet_Activate()
Если xRg ничто, то
Установите xRg = Union(Range("I10:I20"), Range("K10:K20"), Range("M10:M20"), Range("O10:O20"))
End If
End Sub
Private Sub Worksheet_Change (ByVal Target As Range)
Dim I как целое число
Dim xRgNew As Range
Dim xRgLCell как диапазон
On Error Resume Next
Application.EnableEvents = False
Для I = 1 To xRg.Areas.Count
Установить xRgLCell = xRg.Areas.Item(I)
Установить xRgLCell = xRgLCell(xRgLCell.Count).Offset(1, 0)
Если Целевой.Адрес = xRgLCell.Адрес Тогда
Если xRgNew ничто, то
Установите xRgNew = цель
Еще
Установите xRgNew = Union (xRgNew, Target)
End If
End If
Далее
Установите xRg = Union (xRg, xRgNew)
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange (ByVal Target As Range)
При ошибке GoTo Exitsub
Если (Not Intersect(xRg, Target) is Nothing) And (Target.Count = 1) Then
Цель.Смещение(0, 1).Выбрать
End If
Exitsub:
End Sub
выражения. Protect (Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows, AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows, AllowSorting, AllowFiltering, AllowUsingPivotTables)
выражение Переменная, представляюная объект "Таблица ".
Параметры
Имя | Обязательный или необязательный | Тип данных | Описание |
---|---|---|---|
Password | Необязательный | Variant | Строка, которая указывает пароль, чувствительный к делу для таблицы или книги. Если этот аргумент опущен, можно отклонить таблицу или книгу без использования пароля. В противном случае необходимо указать пароль, чтобы отклонить таблицу или книгу. Если вы забудете пароль, вы не сможете отклонить таблицу или книгу. |
Используйте надежные пароли, содержащие строчные и прописные буквы, цифры и знаки. В ненадежных паролях не используются сочетания таких элементов. Надежный пароль: Y6dh!et5. Слабый пароль: House27. Длина паролей должна быть не меньше 8 символов. В парольной фразе лучше использовать 14 или более символов.
Примечания
В предыдущих версиях , если применить этот метод с аргументом UserInterfaceOnly , заданным для True , а затем сохранить книгу, при повторном возобновлении работы весь рабочий таблица (а не только интерфейс) будет полностью защищена. Чтобы повторно включить защиту пользовательского интерфейса после открытия книги, необходимо снова применить этот метод с помощью набора UserInterfaceOnly к True.
Если вы хотите внести изменения в защищенный таблицу, можно использовать метод Protect на защищенной таблице, если пароль предоставлен. Кроме того, другим методом было бы отклонить таблицу, внести необходимые изменения, а затем снова защитить таблицу.
Незащищенная означает, что ячейка может быть заблокирована (диалоговое окно Format Cells), но включена в диапазон, определенный в диалоговом окне Разрешить пользователям изменять диапазоны, и пользователь не защитил диапазон с помощью пароля или был проверен с помощью разрешений NT.
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
expression.Protect (Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows, AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows, AllowSorting, AllowFiltering, AllowUsingPivotTables)
expression A variable that represents a Worksheet object.
Parameters
Name | Required/Optional | Data type | Description |
---|---|---|---|
Password | Optional | Variant | A string that specifies a case-sensitive password for the worksheet or workbook. If this argument is omitted, you can unprotect the worksheet or workbook without using a password. Otherwise, you must specify the password to unprotect the worksheet or workbook. If you forget the password, you cannot unprotect the worksheet or workbook. |
Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords don't mix these elements. Strong password: Y6dh!et5. Weak password: House27. Passwords should be 8 or more characters in length. A pass phrase that uses 14 or more characters is better.
Remarks
In previous versions, if you apply this method with the UserInterfaceOnly argument set to True and then save the workbook, the entire worksheet (not just the interface) will be fully protected when you reopen the workbook. To re-enable the user interface protection after the workbook is opened, you must again apply this method with UserInterfaceOnly set to True.
If you want to make changes to a protected worksheet, it is possible to use the Protect method on a protected worksheet if the password is supplied. Also, another method would be to unprotect the worksheet, make the necessary changes, and then protect the worksheet again.
Unprotected means that the cell may be locked (Format Cells dialog box) but is included in a range defined in the Allow Users to Edit Ranges dialog box, and the user has unprotected the range with a password or has been validated via NT permissions.
Support and feedback
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.
Microsoft Excel предоставляет пользователю несколько, условно выражаясь, уровней защиты - от простой защиты отдельных ячеек до шифрования всего файла шифрами крипто-алгоритмов семейства RC4. Разберем их последовательно.
Уровень 0. Защита от ввода некорректных данных в ячейку
Самый простой способ. Позволяет проверять что именно пользователь вводит в определенные ячейки и не разрешает вводить недопустимые данные (например, отрицательную цену или дробное количество человек или дату октябрьской революции вместо даты заключения договора и т.п.) Чтобы задать такую проверку ввода, необходимо выделить ячейки и выбрать на вкладке Данные (Data) кнопку Проверка данных (Data Validation) . В Excel 2003 и старше это можно было сделать с помощью меню Данные - Проверка (Data - Validation) . На вкладке Параметры из выпадающего списка можно выбрать тип разрешенных к вводу данных:
Уровень 1. Защита ячеек листа от изменений
Мы можем полностью или выборочно запретить пользователю менять содержимое ячеек любого заданного листа. Для установки подобной защиты следуйте простому алгоритму:
- Выделите ячейки, которые не надо защищать (если таковые есть), щелкните по ним правой кнопкой мыши и выберите в контекстном меню команду Формат ячеек(Format Cells) . На вкладке Защита(Protection) снимите флажок Защищаемая ячейка(Locked) . Все ячейки, для которых этот флажок останется установленным, будут защищены при включении защиты листа. Все ячейки, где вы этот флаг снимете, будут доступны для редактирования несмотря на защиту. Чтобы наглядно видеть, какие ячейки будут защищены, а какие - нет, можно воспользоваться этим макросом.
- Для включения защиты текущего листа в Excel 2003 и старше - выберите в меню Сервис - Защита - Защитить лист(Tools - Protection - Protect worksheet) , а в Excel 2007 и новее - нажмите кнопку Защитить лист (Protect Sheet) на вкладке Рецензирование (Reveiw) . В открывшемся диалоговом окне можно задать пароль (он будет нужен, чтобы кто попало не мог снять защиту) и при помощи списка флажков настроить, при желании, исключения:
Т.е., если мы хотим оставить пользователю возможность, например, форматировать защищенные и незащищенные ячейки, необходимо установить первых три флажка. Также можно разрешить пользователям использовать сортировку, автофильтр и другие удобные средства работы с таблицами.
Уровень 2. Выборочная защита диапазонов для разных пользователей
Если предполагается, что с файлом будут работать несколько пользователей, причем каждый из них должен иметь доступ в свою область листа, то можно установить защиту листа с разными паролями на разные диапазоны ячеек.
Чтобы сделать это выберите на вкладке Рецензирование (Review) кнопку Разрешить изменение диапазонов (Allow users edit ranges) . В версии Excel 2003 и старше для этого есть команда в меню Сервис - Защита - Разрешить изменение диапазонов (Tools - Protection - Allow users to change ranges) :
В появившемся окне необходимо нажать кнопку Создать (New) и ввести имя диапазона, адреса ячеек, входящих в этот диапазон и пароль для доступа к этому диапазону:
Повторите эти действия для каждого из диапазонов разных пользователей, пока все они не окажутся в списке. Теперь можно нажать кнопку Защитить лист (см. предыдущий пункт) и включить защиту всего листа.
Теперь при попытке доступа к любому из защищенных диапазонов из списка, Excel будет требовать пароль именно для этого диапазона, т.е. каждый пользователь будет работать "в своем огороде".
Уровень 3. Защита листов книги
Если необходимо защититься от:
- удаления, переименования, перемещения листов в книге
- изменения закрепленных областей ("шапки" и т.п.)
- нежелательных изменений структуры (сворачивание строк/столбцов при помощи кнопок группировки "плюс/минус")
- возможности сворачивать/перемещать/изменять размеры окна книги внутри окна Excel
то вам необходима защита всех листов книги, с помощью кнопки Защитить книгу (Protect Workbook) на вкладке Рецензирование (Reveiw) или - в старых версиях Excel - через меню Сервис - Защита - Защитить книгу (Tools - Protection - Protect workbook) :
Уровень 4. Шифрование файла
При необходимости, Excel предоставляет возможность зашифровать весь файл книги, используя несколько различных алгоритмов шифрования семейства RC4. Такую защиту проще всего задать при сохранении книги, т.е. выбрать команды Файл - Сохранить как (File - Save As) , а затем в окне сохранения найти и развернуть выпадающий список Сервис - Общие параметры (Tools - General Options) . В появившемся окне мы можем ввести два различных пароля - на открытие файла (только чтение) и на изменение:
Предположим, у вас есть рабочий лист, и только определенный диапазон пустых ячеек требует ввода данных, и после завершения ввода данных вам нужно, чтобы ячейки были автоматически заблокированы, чтобы предотвратить повторные изменения. Как вы можете этого добиться? Эта статья может вам помочь.
Блокировать или защищать ячейки после ввода данных или ввода с помощью кода VBA
Например, определенный диапазон пустых ячеек - A1: F8. Чтобы заблокировать эти ячейки после ввода данных в Excel, сделайте следующее.
1. Сначала разблокируйте этот диапазон, выберите ячейки и щелкните правой кнопкой мыши, затем выберите Формат ячеек в контекстном меню и в Формат ячеек диалоговое окно, сняв флажок Заблокированный поле под защиту вкладка и, наконец, щелкнув OK кнопка. Смотрите скриншот:
2. Нажмите Обзор > Защитить лист. И укажите пароль для защиты этого рабочего листа.
3. Щелкните правой кнопкой мыши вкладку листа и выберите Просмотреть код из контекстного меню. Затем скопируйте и вставьте приведенный ниже код VBA в окно кода. Смотрите скриншот:
Код VBA: блокировка или защита ячеек после ввода или ввода данных
Внимание: В коде «A1: F8» - это диапазон, который вам нужен для ввода данных; и «123» - пароль этого защищенного рабочего листа. Пожалуйста, измените их по своему усмотрению.
4. Нажмите другой + Q клавиши одновременно, чтобы закрыть Microsoft Visual Basic для приложений окно.
После завершения ввода данных в ячейки диапазона A1: F8 они будут заблокированы автоматически. И вы получите диалоговое окно с запросом, если попытаетесь изменить содержимое любой ячейки этого диапазона. Смотрите скриншот:
Office Tab - Tabbed Browsing, Editing, and Managing of Workbooks in Excel:
Office Tab brings the tabbed interface as seen in web browsers such as Google Chrome, Internet Explorer new versions and Firefox to Microsoft Excel. It will be a time-saving tool and irreplaceble in your work. See below demo:
Читайте также: