Запрет на редактирование ячейки vba excel
В любом случае, этот макрос может запускаться автоматически при открытии файла или при нажатии любой ячейки. Я не хочу, чтобы людям приходилось запускать макрос вручную, чтобы защитить нужные диапазоны.
Кроме того, мне нужно будет защитить 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
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) . В появившемся окне мы можем ввести два различных пароля - на открытие файла (только чтение) и на изменение:
Добрый вечер .
пожалуйста подскажите макрос который бы запрещал редактирование ячеек определенного цвета. Именно запретить Ввод данных в них.
В документе есть ряд ячеек имеющих красный цвет заливки
Нужно сделать так чтобы при условии если ячейка имеет заливку красного цвета ее редактирование становилось не возможным.
Реально ли такое сделать?
По значению в ячейке выделить определённым цветом ячейку
Если количество символов в ячейке больше 15, то выделить цветом эту ячейку без макросов.
По значению в ячейке выделить определённым цветом всю строку
Добрый вечер! Ну, пожалуйста, необходим макрос: В определённом столбце(R) если значение в ячейке.
Создать макрос, который данные в выделенной области выводит красным цветом на светло-голубом фоне
помогите пожалуйста создать макрос, который данные в выделенной области выводит красным цветом.
Выделение цветом текста в ячейке
Есть таблица в Excel, в каждой ячейке содержится название и описание какой-либо операции.
Попробуйте что-нибудь ввести в файле, который находится во вложении, в ячейки, выделенные красным цветом
esboy21, насколько устойчивой должна быть защита и почему Вы не хотите использовать штатные средства? Макросом можно включить защиту ячеек по цвету. Способ chumich можно обойти путем копирования-вставки.
Разве? А я думал, чтобы что-то вставить в ячейку, ее нужно сначала выделить Казанский, можете продемонстрировать на приложенном файле? А то у меня не получается
chumich, пожалуйста: в I2 пишем что-то, выделяем I1:I2, копируем, выделяем F3, вставляем.
Если бы еще цвет при этом не менялся, можно было бы в Worksheet_Change отследить изменение ячейки с цветом и сделать application.Undo, а так придется, например, при открытии книги запомнить диапазон красных ячеек и в Worksheet_Change сравнивать с ним.
Опять же непонятно, хочет ли esboy21 добавлять красные ячейки в процессе работы с книгой?
Ни в коем случае не отказываясь от предыдущего решения, потому что так и не получил подтверждения того, что оно не работает , сделал вариант с использованием стандартных средств (предварительно нужно снять блокировку ячеек со всего листа):
Почему? F3 - не красная. Выделяю как угодно - мышью, стрелками, вводя F3 в поле Имя или в Ctrl+G - ссылка.
Понял . С F4 перепутал. Но, в таком случае и стандартная защита, по-моему, не сработает. Или придется весь лист блокировать от изменений, или устанавливать заранее блокировку выделенных ячеек. В смысле, сначала снимать блокировку со всех, потом устанавливать, вручную, на красные. Потом ставить защиту на лист. Думаю, речь идет о случайных изменениях, и ТС имел ввиду обычного пользователя, который в процессе случайно выделит и изменит значение ячейки. Не думаю, что найдется злоумышленник вашего уровня знаний и сообразительности, который сделает так, как сделали вы . Так, что оба варианта сработают.
Ячейки выделенные красным цветом будут появляться в процессе работы с книгой.
Для них создано условие для заливки красным цветом.
Часть ячеек добавляется в конец таблицы путем копирования
Потом по результатам выполняется ли условие или нет ячейки заливается красным цветом,
Если ячейки без заливки то они должны редактироваться
esboy21, вы еще не пояснили один момент: это защита от случайного непреднамеренного изменения данных или кто-то будет стараться и искать способы изменить данные в этих ячейках.
Но текст в ячейки залитые красным вводится .
И еще одна трудность с условным форматированием диапазона ячеек.
Нужно если дата в столбце A соответствует вызодному дню суб. или вс. залить диапазон ячеек в диапазоне от C3 до Q 12 красным.
И если выделить и скопировать диапазон от A3 до Q13 условие форматирование сохранялось для всего форматируемого диапаона.
Получается так что условие сохраняется для одной ближайшей к дате ячейке из столбца С
chumich, это защита от случайного ввода данных .
Если день выходной то данные в таблицу вводить нельзя . Вот чего нужно избежать
на примере первой даты в таблице
В столбце А указана дата .
А3 в примере первого дня
И если эта дата соответствует субботе или воскресенью то при помощи условного форматирования ячейки в диапазоне от С3 до Q12 должны окраситься в красный цвет
Для этого исполльзую формулу следующего вида в условном форматировании =ДЕНЬНЕД($A$3;2)=6 и =ДЕНЬНЕД($A$3;2)=7
и чтобы продолжать таблицу я выделяю ячейки пренадлежащие последнему заполненному дню и копирую вставляя ниже
При вставке условное форматирование срабатывает только для одной ячейки а не целого диапазона.
Предположим, у вас есть рабочий лист, и только определенный диапазон пустых ячеек требует ввода данных, и после завершения ввода данных вам нужно, чтобы ячейки были автоматически заблокированы, чтобы предотвратить повторные изменения. Как вы можете этого добиться? Эта статья может вам помочь.
Блокировать или защищать ячейки после ввода данных или ввода с помощью кода 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:
выражения. 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 и обратная связь.
Читайте также: