Note: The other languages of the website are Google-translated. Back to English

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

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

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


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

Предположим, вам нужно заблокировать ячейки 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-дневная гарантия возврата денег.
вкладка kte 201905

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

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
офисный дно
Сортировать комментарии по
Комментарии (22)
Оценок пока нет. Оцените первым!
Этот комментарий был сведен к минимуму модератором на сайте
Я хотел скрыть формулу в ячейке O1. Пожалуйста, сообщите формулу для того же.
Этот комментарий был сведен к минимуму модератором на сайте
Дорогой Аджай,
Чтобы скрыть формулу ячеек, перейдите по гиперссылке ниже, чтобы получить решение.
https://www.extendoffice.com/documents/excel/1424-excel-hide-formulas.html
Этот комментарий был сведен к минимуму модератором на сайте
Дорогой Аджай,
Если вы хотите скрыть формулу в ячейке O1 без защиты рабочего листа, попробуйте приведенный ниже сценарий VBA.
Private Sub Worksheet_SelectionChange (ByVal Target As Range)
Статическая формула в виде строки
Если Target.Address = "$O$1" Тогда
С целью
Формула = .Формула
.Значение = .Значение
Конец с
Еще
С диапазоном ("O1")
Если Не .HasFormula Тогда
.Формула = Формула
End If
Конец с
End If
End Sub

После использования кода кажется, что формула ячейки O1 изменена на результат формулы. Фактически, он скрывает формулу с отображением результата формулы в строке формул. И формула будет отображаться, если код не работает.
Этот комментарий был сведен к минимуму модератором на сайте
как я могу заблокировать ряд строк, скажем, 4-46 и столбцы 8 и 10
Этот комментарий был сведен к минимуму модератором на сайте
Уважаемый ЭйДжей,
Если вы хотите заблокировать диапазон указанных строк и столбцов, попробуйте приведенный ниже сценарий 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
Этот комментарий был сведен к минимуму модератором на сайте
Это отличный обходной путь, особенно в общих книгах, где включение и выключение защиты не поддерживается. Большое спасибо.
Этот комментарий был сведен к минимуму модератором на сайте
Дорогая Кристалл,

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

Как это можно применить только к диапазону таблиц, а не ко всему рабочему листу? Заранее спасибо.
Этот комментарий был сведен к минимуму модератором на сайте
Добрый день,
В соответствии с приведенным ниже кодом 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
Этот комментарий был сведен к минимуму модератором на сайте
После того как вы запустите VBA для блокировки этих ячеек, как вы их разблокируете?
Вам нужен код VBA для разблокировки?
Этот комментарий был сведен к минимуму модератором на сайте
Привет РЕНИК,

Мы не предоставляем разблокированный код 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
Этот комментарий был сведен к минимуму модератором на сайте
Дорогая Кристалл,

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

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

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

Большое спасибо за помощь!
Этот комментарий был сведен к минимуму модератором на сайте
Привет, Эрин,
Вы можете вручную разбить код, нажав кнопку Break в окне Microsoft Visual Basic для приложений, чтобы разблокировать эти диапазоны. И запустите код, чтобы активировать его снова. Спасибо за ваш комментарий.
Этот комментарий был сведен к минимуму модератором на сайте
Привет,
Комментарий Faire pour verrouiller de la cellule B8 à B10000?
D'avance merci de votre réponse.
Christophe
Этот комментарий был сведен к минимуму модератором на сайте
Есть ли какая-либо функция для установки, например, ячейки 2 строки 13 на 900? Или мне нужно вручную пробивать каждое имя ячейки в коде?
Этот комментарий был сведен к минимуму модератором на сайте
Здравствуйте,

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

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

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


Спасибо
Этот комментарий был сведен к минимуму модератором на сайте
У меня попробовать нельзя, Скрыть формулу без защиты.
Этот комментарий был сведен к минимуму модератором на сайте
Есть ли способ, чтобы этот код VBA запускался автоматически каждый раз, когда кто-то открывает файл?
Этот комментарий был сведен к минимуму модератором на сайте
Привет, Аарон! После добавления кода сохраните книгу как книгу Excel с поддержкой макросов (нажмите Файл > Сохранить как > указать папку для файла > выбрать Excel Macro-Enabled Workbook из Сохранить как введите раскрывающийся список > Сохраните). После этого каждый раз при открытии файла код срабатывает автоматически.
Здесь еще нет комментариев
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места

Подписывайтесь на Нас

Copyright © 2009 - www.extendoffice.ком. | Все права защищены. Питаться от ExtendOffice, | Карта сайта
Microsoft и логотип Office являются товарными знаками или зарегистрированными товарными знаками Microsoft Corporation в США и / или других странах.
Защищено Sectigo SSL