Как заблокировать указанные ячейки без защиты всего листа в Excel?

Как правило, вам необходимо защитить весь рабочий лист, чтобы заблокировать ячейки от редактирования. Есть ли способ заблокировать ячейки без защиты всего рабочего листа? Эта статья рекомендует вам метод VBA.

Блокируйте указанные ячейки без защиты всего рабочего листа с помощью VBA

Блокируйте указанные ячейки без защиты всего рабочего листа с помощью VBA

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

Предположим, вам нужно заблокировать ячейки A3 и A5 на текущем листе, следующий код VBA поможет вам добиться этого, не защищая весь лист.

1. Щелкните правой кнопкой мыши вкладку листа и выберите Просмотреть код из контекстного меню.

2. Затем скопируйте и вставьте приведенный ниже код VBA в окно кода. Смотрите скриншот:

Код VBA: блокировка указанных ячеек без защиты всего рабочего листа

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 1 Then
        If Target.Row = 3 Or Target.Row = 5 Then
            Beep
            Cells(Target.Row, Target.Column).Offset(0, 1).Select
        End If
    End If
End Sub

Внимание: В коде Колонка 1, Ряд = 3 и Ряд = 5 указывает, что ячейки A3 и A5 на текущем листе будут заблокированы после запуска кода. Вы можете изменить их по своему усмотрению.

3. нажмите другой + Q клавиши одновременно, чтобы закрыть Microsoft Visual Basic для приложений окно.

Теперь ячейки A3 и A5 заблокированы на текущем листе. Если вы попытаетесь выбрать ячейку A3 или A5 на текущем листе, курсор автоматически переместится в правую соседнюю ячейку.

Статьи по теме:

Лучшие инструменты для работы в офисе

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма ...
Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы... Предотвращение дублирования ячеек; Сравнить диапазоны...
Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии...
Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом ...
Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.

Больше информации... Скачать бесплатно... Покупка ...

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

Больше информации... Скачать бесплатно... Покупка ...

Комментарии (22)

Оценок пока нет. Оцените первым!

Аджай

о 5 лет назад

#20148

Этот комментарий был сведен к минимуму модератором на сайте

Я хотел скрыть формулу в ячейке O1. Пожалуйста, сообщите формулу для того же.

Ответить

кристалл Аджай

о 5 лет назад

#20149

Этот комментарий был сведен к минимуму модератором на сайте

Дорогой Аджай,
Чтобы скрыть формулу ячеек, перейдите по гиперссылке ниже, чтобы получить решение.
https://www.extendoffice.com/documents/excel/1424-excel-hide-formulas.html

Ответить

Report

кристалл Аджай

о 5 лет назад

#20150

Этот комментарий был сведен к минимуму модератором на сайте

Дорогой Аджай,
Если вы хотите скрыть формулу в ячейке O1 без защиты рабочего листа, попробуйте приведенный ниже сценарий VBA.
Private Sub Worksheet_SelectionChange (ByVal Target As Range)
Статическая формула в виде строки
Если Target.Address = "$O$1" Тогда
С целью
Формула = .Формула
.Значение = .Значение
Конец с
Еще
С диапазоном ("O1")
Если Не .HasFormula Тогда
.Формула = Формула
End If
Конец с
End If
End Sub

После использования кода кажется, что формула ячейки O1 изменена на результат формулы. Фактически, он скрывает формулу с отображением результата формулы в строке формул. И формула будет отображаться, если код не работает.

Ответить

Report

о 5 лет назад

#21550

Этот комментарий был сведен к минимуму модератором на сайте

как я могу заблокировать ряд строк, скажем, 4-46 и столбцы 8 и 10

Ответить

кристалл AJ

о 5 лет назад

#21551

Этот комментарий был сведен к минимуму модератором на сайте

Уважаемый ЭйДжей,
Если вы хотите заблокировать диапазон указанных строк и столбцов, попробуйте приведенный ниже сценарий VBA.

Private Sub Worksheet_SelectionChange (ByVal Target As Range)
Dim xRg как диапазон
Dim xRgEx как диапазон
Dim xRgExEach как диапазон
On Error Resume Next
Приложение.ScreenUpdating = False
Set xRg = Range("H:J,4:46") 'Измените диапазон строк и диапазон столбцов, которые вы заблокируете, не защищая рабочий лист
Установите xRgEx = Application.Intersect(xRg, Target)
Если xRgEx ничего не значит, выйдите из Sub
Ячейки (1, 1). Выберите «Укажите ячейку, в которую вы перейдете после выбора заблокированных ячеек».
Application.ScreenUpdating = True
End Sub

Ответить

Report

Никки кристалл

о 2 лет назад

#21552

Этот комментарий был сведен к минимуму модератором на сайте

Это отличный обходной путь, особенно в общих книгах, где включение и выключение защиты не поддерживается. Большое спасибо.

Ответить

Анхель Пабло Искьердо Молино

о 5 лет назад

#21942

Этот комментарий был сведен к минимуму модератором на сайте

Дорогая Кристалл,

Вы предоставили мне решение (половину), с которым я боролся в течение последних нескольких недель, но мне нужны еще некоторые подсказки.

Как это можно применить только к диапазону таблиц, а не ко всему рабочему листу? Заранее спасибо.

Ответить

Report

кристалл Анхель Пабло Искьердо Молино

о 5 лет назад

#21943

Этот комментарий был сведен к минимуму модератором на сайте

Добрый день,
В соответствии с приведенным ниже кодом VBA измените указанный диапазон «H: J, 4: 46» на диапазон таблицы, который вы хотите заблокировать только на листе.
И ячейки (1,1) должны быть ячейкой вне диапазона таблицы. При нажатии на любую ячейку в диапазоне таблицы курсор автоматически перемещается в эту ячейку.

Private Sub Worksheet_SelectionChange (ByVal Target As Range)
Dim xRg как диапазон
Dim xRgEx как диапазон
Dim xRgExEach как диапазон
On Error Resume Next
Приложение.ScreenUpdating = False
Set xRg = Range("H:J,4:46") 'Измените диапазон строк и диапазон столбцов, которые вы заблокируете, не защищая рабочий лист
Установите xRgEx = Application.Intersect(xRg, Target)
Если xRgEx ничего не значит, выйдите из Sub
Ячейки (1, 1). Выберите «Укажите ячейку, в которую вы перейдете после выбора заблокированных ячеек».
Application.ScreenUpdating = True
End Sub

Ответить

Report

РЕНИК кристалл

около 2 недель назад

#42633

Этот комментарий был сведен к минимуму модератором на сайте

После того как вы запустите VBA для блокировки этих ячеек, как вы их разблокируете?
Вам нужен код VBA для разблокировки?

Ответить

кристалл РЕНИК

около 5 дней назад

#42874

Этот комментарий был сведен к минимуму модератором на сайте

Привет РЕНИК,

Мы не предоставляем разблокированный код VBA, взламываем код вручную, после чего область можно редактировать в обычном режиме.

Ответить

Report

Чарли

о 5 лет назад

#23481

Этот комментарий был сведен к минимуму модератором на сайте

Дорогая Кристалл,

В любом случае, этот макрос может запускаться автоматически при открытии файла или при нажатии любой ячейки. Я не хочу, чтобы людям приходилось запускать макрос вручную, чтобы защитить нужные диапазоны.

Кроме того, мне нужно будет защитить 12 несмежных диапазонов. Например: I11:I20 и K11:K20 и M11:20 и т.д... Как мне это сделать?

И последнее, и здесь может быть слишком много вопросов, но можно ли применить защиту к приведенным выше примерам диапазонов, НО затем распространить защиту на дополнительные строки по мере добавления новых? Другими словами, защита будет применяться к I11:I20 и K11:K20 и M11:20, но пользователь сможет добавить новую строку (строка 21) с новыми данными, но как только новая строка будет добавлена, тогда защита будет применяться к I11:I21 и K11:K21 и M11:21. Я прошу луну? :-)

Спасибо за все, что вы уже предоставили! Я не могу отблагодарить вас и других людей, подобных вам, за то, что вы поделились своими знаниями. Удивительно!

Ответить

Report

кристалл Чарли

о 4 лет назад

#23482

Этот комментарий был сведен к минимуму модератором на сайте

Дорогой Карлос,
Следующий код 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

Ответить

Report

Эрин

о 4 лет назад

#25482

Этот комментарий был сведен к минимуму модератором на сайте

Дорогая Кристалл,

Спасибо вам большое за это! Это работает отлично.

Я использовал код в комментарии Карлоса для автоматического запуска макроса при открытии файла. Мне было интересно, есть ли способ иметь кнопку «Отменить» или что-то подобное, которая позволяет вам отменить этот код и, следовательно, разблокировать те диапазоны, которые были заблокированы.

Я хочу, чтобы они были заблокированы большую часть времени, но я хотел бы разблокировать их, если мне нужно будет изменить какие-либо данные. Единственная причина, по которой я не защищаю весь лист, заключается в том, что если я это сделаю, это деактивирует возможность расширения таблиц. Это, в свою очередь, разворачивается на линейной диаграмме.

Большое спасибо за помощь!

Ответить

кристалл Эрин

о 4 лет назад

#25483

Этот комментарий был сведен к минимуму модератором на сайте

Привет, Эрин,
Вы можете вручную разбить код, нажав кнопку Break в окне Microsoft Visual Basic для приложений, чтобы разблокировать эти диапазоны. И запустите код, чтобы активировать его снова. Спасибо за ваш комментарий.

Ответить

Report

Кристоф Буркар

о 4 лет назад

#26946

Этот комментарий был сведен к минимуму модератором на сайте

Привет,
Комментарий Faire pour verrouiller de la cellule B8 à B10000?
D'avance merci de votre réponse.
Christophe

Ответить

Report

Эспен

о 4 лет назад

#27872

Этот комментарий был сведен к минимуму модератором на сайте

Есть ли какая-либо функция для установки, например, ячейки 2 строки 13 на 900? Или мне нужно вручную пробивать каждое имя ячейки в коде?

Ответить

Чарли

о 3 лет назад

#28136

Этот комментарий был сведен к минимуму модератором на сайте

Здравствуйте,

Я использовал код, предоставленный Карлосу, и он сделал именно то, что я хотел. Есть ли способ сместить некоторые диапазоны в ROW справа от защищенного диапазона (как это уже делает код «Carlos»), но сместить другие диапазоны в COLUMN на ячейку непосредственно под защищенным диапазоном? Я попытался дважды ввести код «Карлос» и изменить смещение, но получил множество ошибок.

спасибо

Ответить

кристалл Чарли

о 3 лет назад

#28137

Этот комментарий был сведен к минимуму модератором на сайте

Привет, Чарли,
Извините, пока не могу вам с этим помочь. Спасибо за ваш комментарий.

Ответить

Report

Кейси

о 3 лет назад

#29980

Этот комментарий был сведен к минимуму модератором на сайте

Здравствуйте,

Я пытался использовать код для диапазонов, который вы ранее опубликовали, но он не работает. Не могли бы вы посоветовать мне, следует ли мне комбинировать код для диапазонов выше или ниже?

Спасибо

Ответить

Вонг Пегги

о 2 лет назад

#33015

Этот комментарий был сведен к минимуму модератором на сайте

У меня попробовать нельзя, Скрыть формулу без защиты.

Ответить

Report

Аарон

о 2 лет назад

#34545

Этот комментарий был сведен к минимуму модератором на сайте

Есть ли способ, чтобы этот код VBA запускался автоматически каждый раз, когда кто-то открывает файл?

Ответить

кристалл Аарон

о 2 лет назад

#34546

Этот комментарий был сведен к минимуму модератором на сайте

Привет, Аарон! После добавления кода сохраните книгу как книгу Excel с поддержкой макросов (нажмите Файл > Сохранить как > указать папку для файла > выбрать Excel Macro-Enabled Workbook из Сохранить как введите раскрывающийся список > Сохраните). После этого каждый раз при открытии файла код срабатывает автоматически.

Ответить

Report

Здесь еще нет комментариев

Оставляйте свои комментарии

Публикация как гость

Логин

Имя (обязательно)

E-mail (обязательно)

Оценить этот пост:

Сброс

0 Характеристики

Я согласен с условия и сроки