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

Як зберегти таблицю розширюваною, вставляючи рядок таблиці в захищений аркуш у Excel?

Функція автоматичного розширення таблиці буде втрачена після захисту робочого аркуша в Excel. Наприклад, на вашому захищеному аркуші є таблиця з назвою Table1, коли ви вводите що-небудь під останнім рядком, таблиця автоматично не розширюватиметься, щоб включити новий рядок. Чи існує спосіб зберегти таблицю розширюваною, вставляючи новий рядок у захищений аркуш? Метод у цій статті може допомогти вам його досягти.

Зберігайте таблицю розширюваною, вставляючи рядок таблиці в захищений аркуш із кодом VBA


Зберігайте таблицю розширюваною, вставляючи рядок таблиці в захищений аркуш із кодом VBA


Як показано на знімку екрана, таблиця з іменем Table1 на вашому аркуші, а останній стовпець таблиці - це стовпець формули. Тепер вам потрібно захистити робочий аркуш, щоб запобігти зміні стовпця формули, але дозволити розгорнути таблицю, вставивши новий рядок і призначивши нові дані в нові клітинки. Будь ласка, виконайте наступне.

1. клацання Розробник > Insert > Кнопка (контроль форми) вставити a Контроль форми на робочому аркуші.

2. У спливаючому Призначити макрос у діалоговому вікні натисніть новий кнопки.

3 В Microsoft Visual Basic для додатків вікно, скопіюйте та вставте наведений нижче код VBA між нижче і End Sub абзаців у код вікна.

Код VBA: Зберігайте таблицю розширюваною, вставляючи рядок таблиці в захищений аркуш

Dim pswStr As String
'Update by ExtendOffice 20181106
    pswStr = "123"
    On Error Resume Next
   Application.ScreenUpdating = False
    ActiveSheet.Unprotect Password:=pswStr
       ActiveSheet.Range("A1").Select
        Range("Table1[[#Headers],[Total]]").Select
        Selection.End(xlDown).Select
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "new"
        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
    Selection.ClearContents
    Application.ScreenUpdating = True

примітки:

1). У коді число «123» - це пароль, який ви будете використовувати для захисту робочого аркуша.
2). Будь ласка, змініть назву таблиці та назву стовпця, що містить формулу, яку ви захищатимете.
3). І змініть зміщення в коді Selection.Offset (1, -4). Виберіть у перший цільовий стовпець таблиці. Наприклад, якщо в таблиці є шість стовпців, змініть значення Зсув на (1, -5).

4 Натисніть кнопку інший + Q клавіші, щоб закрити вікно Microsoft Visual Basic for Applications.

5. Виділіть клітинки таблиці, до яких потрібно призначити нові дані, крім стовпця формули, а потім натисніть Ctrl + 1 ключі, щоб відкрити Формат ячеек діалогове вікно. В Формат ячеек діалоговому вікні, зніміть прапорець біля Заблокований , а потім клацніть на OK кнопку. Дивіться знімок екрана:

6. Тепер захистіть свій аркуш паролем, який ви вказали в коді VBA.

Відтепер після натискання кнопки «Форма керування» на захищеному аркуші таблицю можна буде розширювати, вставляючи новий рядок, як показано нижче.

примітки: ви можете змінити таблицю, крім стовпця формули на захищеному аркуші.


Статті по темі:


Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

  • Повторне використання: Швидко вставте складні формули, діаграми і все, що ви використовували раніше; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
  • Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
  • Об’єднати клітинки / рядки / стовпці без втрати даних; Вміст розділених комірок; Об'єднати повторювані рядки / стовпці... Запобігання дублюючим клітинам; Порівняйте діапазони...
  • Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
  • Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
  • Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
  • Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
  • Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
  • Понад 300 потужних функцій. Підтримує Office / Excel 2007-2019 та 365. Підтримує всі мови. Простота розгортання на вашому підприємстві чи в організації. Повна функція 30-денної безкоштовної пробної версії. 60-денна гарантія повернення грошей.
вкладка kte 201905

Вкладка Office забезпечує інтерфейс з вкладками для Office і значно спрощує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Коментарі (12)
Поки немає оцінок. Оцініть першим!
Цей коментар був мінімізований модератором на сайті
Тож я спробував це, але він додає новий рядок у нижній частині книги в рядку 1048576, однак моя таблиця містить лише близько 800 записів. Я поняття не маю, чому він це робить!
Цей коментар був мінімізований модератором на сайті
Привіт Брінді!
Код оновлено, проблема вирішена, будь ласка, спробуйте та дякуємо за коментар.
Цей коментар був мінімізований модератором на сайті
Привіт, Кристал, проблема та сама. Я створив для себе нову таблицю лише з 2 рядками. Після натискання кнопки список розгортається до кінця таблиці без додавання рядків. Його слід додати до рядка номер 3.
Цей коментар був мінімізований модератором на сайті
Привіт, Кристал, проблема та сама. Я створив для себе нову таблицю лише з 2 рядками. Після натискання кнопки список розгортається до кінця таблиці без додавання рядків. Його слід додати до рядка номер 3.
Цей коментар був мінімізований модератором на сайті
Спробуйте цей код Vba, щоб додати новий рядок у вашу таблицю

Підкладка_рядка_Додати()
Dim pswStr як рядок
pswStr = "123"
On Error Resume Next
Application.ScreenUpdating = Невірний
ActiveSheet.Unprotect Password:=pswStr
ActiveSheet.Range("D8").Вибрати
«D8 — заголовок таблиці
Діапазон("Таблиця1[[#Заголовки],[Усього]]"). Виберіть
Selection.End(xlDown).Select
Selection.ListObject.ListRows.Add AlwaysInsert:=False
ActiveSheet.Protect Пароль:=pswStr

End Sub
.
Цей коментар був мінімізований модератором на сайті
Привіт Мак!
Дякую, що поділилися.
Цей коментар був мінімізований модератором на сайті
Використання запропонованого (Selection.ListObject.ListRows.Add AlwaysInsert:=False) вирішує подібну проблему для мене з оригінальним кодом, де новий повний рядок (розширює клітинку, що містить формули) не буде додано до таблиці на набагато ширшому таблиця 51 колонка. Тож дякую, що поділилися та виправили Mac.
Цей коментар був мінімізований модератором на сайті
Привіт, я використав наведений вище код і отримав таке повідомлення про помилку:
«Виконання коду перервано». Коли я клацаю на Debug, рядок 20 "Selection.ClearContents" виділено.

Коли я спочатку ввів код, він працював правильно.

Я змінив "Таблицю" на назву таблиці та змінив стовпець на назву стовпця, який я використовую. Я також змінив "Selection.Offset (x,-x).Select" відповідно до своїх потреб.


Будь-які пропозиції щодо того, чому це відбувається?
Цей коментар був мінімізований модератором на сайті
Здравствуйте,

спочатку код працював, але після того, як я скопіював аркуш, він залишився протягом 24 годин, потім весь код зник. А тепер я не можу отримати доступ до робочого аркуша.

він постійно говорить мені неправильний пароль. І код зник. .
Цей коментар був мінімізований модератором на сайті
Merhaba Tablo ismini ve satır başlangıc yerlerini değiştirdiğim zaman kod çalışmıyor yardımcı olurmusunuz
Цей коментар був мінімізований модератором на сайті
привіт,
Переконайтеся, що ви змінили в коді те саме ім’я таблиці та заголовок стовпця.
Я змінив назву таблиці та заголовок стовпця, щоб перевірити код, і він працює добре.
Ви отримували повідомлення про помилку? Мені потрібно дізнатися більше про вашу проблему, наприклад, вашу версію Excel. Чим детальніше ви опишете помилку, тим швидше ми зможемо її зрозуміти та вирішити.
Цей коментар був мінімізований модератором на сайті
Як створити кнопку для стирання рядків?
There are no comments posted here yet
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця