Как сохранить возможность расширения таблицы за счёт вставки строк на защищённом листе в Excel?
В Excel при защите листа функция автоматического расширения таблиц часто отключается. Например, если у вас есть таблица с именем Table1 и вы попытаетесь добавить данные под последней строкой на защищённом листе, таблица не расширится автоматически, чтобы включить новую строку. Однако есть способ сохранить возможность автоматического расширения таблицы даже на защищённом листе. В этом руководстве пошагово описано, как включить автоматическое расширение таблицы, не отключая защиту листа.
Сохранение возможности расширения таблицы путём вставки строки таблицы на защищённом листе с помощью кода VBA
Как показано на снимке экрана ниже, на вашем листе расположена таблица с именем Table1, а её последний столбец — это формула. Чтобы защитить лист от изменений в этом столбце, но при этом разрешить расширение таблицы за счёт добавления новых строк и ввода данных в новые ячейки, выполните следующие действия.
1. Нажмите Разработчик > Вставить > Кнопка (элемент управления формы), чтобы добавить на лист элемент управления формы.
2. В появившемся диалоговом окне Назначить макрос нажмите кнопку Создать.
3. В окне Microsoft Visual Basic для приложений скопируйте и вставьте приведённый ниже код VBA между строками Sub и End Sub в окне Код.
Код VBA: Сохранение возможности расширения таблицы путём вставки строки таблицы на защищённом листе
'Update by ExtendOffice 20220826
Dim xRg, tableRg As Range
Dim xRowCount As Integer
Dim pswStr As String
pswStr = "123"
On Error Resume Next
Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:=pswStr
Set tableRg = ActiveSheet.ListObjects("Table4").Range
xRowCount = tableRg.Rows.Count
Set xRg = Range("Table4[[#Headers],[Total]]").Offset(1, 0)
Set yRg = xRg.Resize(xRowCount, 1)
xRg.Resize(xRowCount - 1, 1).AutoFill Destination:=yRg, Type:=xlFillDefault
ActiveSheet.Protect Password:=pswStr, DrawingObjects:=False, _
Contents:=True, Scenarios:=False, _
AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True, _
AllowInsertingRows:=True, AllowInsertingHyperlinks:=True, _
AllowDeletingColumns:=True, AllowDeletingRows:=True, _
AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True
Application.ScreenUpdating = TrueПримечания:
4. Нажмите клавиши Alt+Q, чтобы закрыть окно Microsoft Visual Basic для приложений.
5. Выделите ячейки таблицы, в которые нужно ввести новые данные (кроме столбца с формулой), затем нажмите клавиши Ctrl+1, чтобы открыть диалоговое окно Установить формат ячейки. В диалоговом окне Установить формат ячейки снимите флажок Защищаемая ячейка и нажмите кнопку ОК. См. снимок экрана:
6. Теперь защитите лист с помощью пароля, заданного в коде VBA.
Теперь при нажатии кнопки элемента управления формы на защищённом листе таблица будет автоматически расширяться за счёт вставки новой строки, как показано на снимке экрана ниже.
Примечание: на защищённом листе можно редактировать таблицу, за исключением столбца с формулой.
Связанные статьи:
- Как настроить срез сводной таблицы так, чтобы он прокручивался вместе с листом в Excel?
- Как объединить ячейки в Excel, сохранив их форматирование?
- Как удалить дубликаты в Excel, оставив только первое вхождение?
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек