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

Làm cách nào để giữ cho bảng có thể mở rộng bằng cách chèn hàng của bảng trong một trang tính được bảo vệ trong Excel?

Chức năng tự động mở rộng của bảng sẽ bị mất sau khi bảo vệ trang tính trong Excel. Ví dụ: có một bảng tên là Table1 trong trang tính được bảo vệ của bạn, khi bạn nhập bất kỳ thứ gì dưới hàng cuối cùng, bảng sẽ không tự động mở rộng để bao gồm hàng mới. Có phương pháp nào để giữ cho bảng có thể mở rộng bằng cách chèn hàng mới vào trang tính được bảo vệ không? Phương pháp trong bài viết này có thể giúp bạn đạt được điều đó.

Giữ bảng có thể mở rộng bằng cách chèn hàng của bảng trong một trang tính được bảo vệ bằng mã VBA


Giữ bảng có thể mở rộng bằng cách chèn hàng của bảng trong một trang tính được bảo vệ bằng mã VBA

Như ảnh chụp màn hình bên dưới được hiển thị, một bảng có tên Table1 trong trang tính của bạn và cột cuối cùng của bảng là cột công thức. Bây giờ bạn cần bảo vệ trang tính để ngăn cột công thức thay đổi, nhưng cho phép mở rộng bảng bằng cách chèn hàng mới và gán dữ liệu mới vào các ô mới. Hãy làm như sau.

1. nhấp chuột Nhà phát triển > Chèn > Nút (Kiểm soát biểu mẫu) để chèn một Kiểm soát biểu mẫu vào trang tính của bạn.

2. Trong cửa sổ bật lên Chỉ định Macro hộp thoại, nhấp vào Mới .

3. bên trong Microsoft Visual Basic cho các ứng dụng cửa sổ, vui lòng sao chép và dán mã VBA bên dưới vào giữa SubEnd Sub đoạn văn trong cửa sổ.

Mã VBA: Giữ cho bảng có thể mở rộng bằng cách chèn hàng bảng trong một trang tính được bảo vệ

 '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

Chú ý:

1). Trong mã, số "123" là mật khẩu bạn sẽ sử dụng để bảo vệ trang tính.
2). Vui lòng thay đổi tên bảng và tên của cột có chứa công thức mà bạn sẽ bảo vệ.

4. Nhấn nút Khác + Q để đóng cửa sổ Microsoft Visual Basic for Applications.

5. Chọn các ô trong bảng mà bạn cần gán dữ liệu mới ngoại trừ cột công thức, sau đó nhấn Ctrl + 1 phím để mở Format Cells hộp thoại. bên trong Format Cells hộp thoại, bỏ chọn Đã bị khoá và sau đó bấm vào OK cái nút. Xem ảnh chụp màn hình:

6. Bây giờ hãy bảo vệ trang tính của bạn bằng mật khẩu bạn đã chỉ định trong mã VBA.

Từ bây giờ, sau khi nhấp vào nút Kiểm soát biểu mẫu trong trang tính được bảo vệ của bạn, bảng sẽ có thể mở rộng bằng cách chèn hàng mới như hình minh họa bên dưới.

Chú thích: bạn có thể sửa đổi bảng ngoại trừ cột công thức trong trang tính được bảo vệ.


Các bài liên quan:


Các công cụ năng suất văn phòng tốt nhất

Kutools cho Excel giải quyết hầu hết các vấn đề của bạn và tăng 80% năng suất của bạn

  • Tái sử dụng: Chèn nhanh công thức phức tạp, biểu đồ và bất cứ thứ gì bạn đã sử dụng trước đây; Mã hóa ô với mật khẩu; Tạo danh sách gửi thư và gửi email ...
  • Thanh siêu công thức (dễ dàng chỉnh sửa nhiều dòng văn bản và công thức); Bố cục đọc (dễ dàng đọc và chỉnh sửa số lượng ô lớn); Dán vào Dải ô đã Lọchữu ích. Cảm ơn !
  • Hợp nhất các ô / hàng / cột mà không làm mất dữ liệu; Nội dung phân chia ô; Kết hợp các hàng / cột trùng lặp... Ngăn chặn các ô trùng lặp; So sánh các dãyhữu ích. Cảm ơn !
  • Chọn trùng lặp hoặc duy nhất Hàng; Chọn hàng trống (tất cả các ô đều trống); Tìm siêu và Tìm mờ trong Nhiều Sổ làm việc; Chọn ngẫu nhiên ...
  • Bản sao chính xác Nhiều ô mà không thay đổi tham chiếu công thức; Tự động tạo tài liệu tham khảo sang Nhiều Trang tính; Chèn Bullets, Hộp kiểm và hơn thế nữa ...
  • Trích xuất văn bản, Thêm Văn bản, Xóa theo Vị trí, Xóa không gian; Tạo và In Tổng số phân trang; Chuyển đổi giữa nội dung ô và nhận xéthữu ích. Cảm ơn !
  • Siêu lọc (lưu và áp dụng các lược đồ lọc cho các trang tính khác); Sắp xếp nâng cao theo tháng / tuần / ngày, tần suất và hơn thế nữa; Bộ lọc đặc biệt bằng cách in đậm, in nghiêng ...
  • Kết hợp Workbook và WorkSheets; Hợp nhất các bảng dựa trên các cột chính; Chia dữ liệu thành nhiều trang tính; Chuyển đổi hàng loạt xls, xlsx và PDFhữu ích. Cảm ơn !
  • Hơn 300 tính năng mạnh mẽ. Hỗ trợ Office / Excel 2007-2021 và 365. Hỗ trợ tất cả các ngôn ngữ. Dễ dàng triển khai trong doanh nghiệp hoặc tổ chức của bạn. Đầy đủ tính năng Dùng thử miễn phí 30 ngày. Bảo đảm hoàn lại tiền trong 60 ngày.
tab kte 201905

Tab Office mang lại giao diện Tab cho Office và giúp công việc của bạn trở nên dễ dàng hơn nhiều

  • Cho phép chỉnh sửa và đọc theo thẻ trong Word, Excel, PowerPoint, Publisher, Access, Visio và Project.
  • Mở và tạo nhiều tài liệu trong các tab mới của cùng một cửa sổ, thay vì trong các cửa sổ mới.
  • Tăng 50% năng suất của bạn và giảm hàng trăm cú nhấp chuột cho bạn mỗi ngày!
officetab dưới cùng
Nhận xét (18)
Chưa có xếp hạng. Hãy là người đầu tiên xếp hạng!
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Vì vậy, tôi đã thử điều này, tuy nhiên nó thêm một hàng mới ở cuối sổ làm việc trên dòng 1048576 tuy nhiên bảng của tôi chỉ có khoảng 800 bản ghi. Tôi không biết tại sao nó lại làm như vậy!
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Chào Brindi,
Mã đã được cập nhật với sự cố đã được giải quyết, hãy dùng thử và cảm ơn bạn đã nhận xét.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào Crystal, vấn đề là giống nhau. Tôi đã tạo một bảng mới cho mình chỉ có 2 hàng. Khi tôi nhấp vào nút, danh sách sẽ được mở rộng cho đến cuối bảng mà không cần thêm bất kỳ Hàng nào. Nó sẽ được thêm vào Hàng số 3.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào Crystal, vấn đề là giống nhau. Tôi đã tạo một bảng mới cho mình chỉ có 2 hàng. Khi tôi nhấp vào nút, danh sách sẽ được mở rộng cho đến cuối bảng mà không cần thêm bất kỳ Hàng nào. Nó sẽ được thêm vào Hàng số 3.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Hãy thử mã Vba này để thêm dòng mới trong bảng của bạn

Tab phụ_Line_Add ()
Dim pswStr dưới dạng chuỗi
pswStr = "123"
On Error Resume Next
Application.ScreenUpdating = Sai
Mật khẩu ActiveSheet.Unprotect: = pswStr
ActiveSheet.Range ("D8"). Chọn
'D8 là tiêu đề tabel
Phạm vi ("Table1 [[# Headers], [Total]]"). Chọn
Selection.End (xlDown) .Select
Selection.ListObject.ListRows.Add AlwaysInsert: = Sai
Mật khẩu ActiveSheet.Protect: = pswStr

End Sub
.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào Mac,
Cảm ơn bạn đã chia sẻ.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
bằng cách sử dụng gợi ý (Selection.ListObject.ListRows.Add AlwaysInsert: = False) đã khắc phục sự cố tương tự đối với tôi với mã gốc, trong đó một hàng đầy đủ mới (kéo dài xuống ô chứa công thức) sẽ không được thêm vào bảng trên phạm vi rộng hơn nhiều bảng 51 cột. Vì vậy, cảm ơn đã chia sẻ và sửa chữa Mac.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào, tôi đã sử dụng mã trên và nhận được thông báo lỗi sau:
"Quá trình thực thi mã đã bị gián đoạn". Khi tôi nhấp vào Gỡ lỗi, Dòng 20 "Selection.ClearContents" được đánh dấu.

Khi tôi nhập mã lần đầu, nó hoạt động chính xác.

Tôi đã thay đổi "Bảng" thành tên của bảng và thay đổi cột thành tên của cột tôi đang sử dụng. Tôi cũng đã thay đổi "Selection.Offset (x, -x) .Select" để phù hợp với nhu cầu của tôi.


Bất kỳ đề xuất tại sao điều này xảy ra?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Chào bạn,

mã hoạt động ban đầu, nhưng sau khi tôi sao chép trang tính, nó vẫn tồn tại trong 24 giờ sau đó tất cả mã biến mất. Và bây giờ tôi không thể truy cập trang tính.

nó tiếp tục cho tôi biết mật khẩu không chính xác. Và mã đã biến mất. .
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Merhaba Tablo ismini ve satır başlangıc yerlerini değiştirdiğim zaman kod çalışmıyor yardımcı olurmusunuz
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Chào,
Đảm bảo rằng bạn đã thay đổi chính xác tên bảng và tiêu đề cột trong mã.
Tôi đã thay đổi tên bảng và tiêu đề cột để kiểm tra mã và nó hoạt động tốt.
Bạn có nhận được bất kỳ lời nhắc lỗi nào không? Tôi cần biết cụ thể hơn về vấn đề của bạn, chẳng hạn như phiên bản Excel của bạn. Bạn mô tả lỗi càng chi tiết, chúng tôi càng có thể hiểu và giải quyết lỗi nhanh hơn.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Làm cách nào để tạo nút xóa dòng?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào!!!
Tengo una tabla donde more de una columna está protegida.
Tabla tiene 17 columnas de las cuales 7 deben quedar bloqueadas porque poseen công thức.
Mi tabla Arnca en celda A4

Estaba tratando de usar este código para probarlo, cambiando lo que verán abajo como "CLAVE", "MITABLA" y "AVISO 1" bởi mis nombres speculares:
Donde "AVISO 1" tương ứng với una de las columnas que está protegida.

Dim pswStr dưới dạng chuỗi
'Cập nhật bởi ExtendOffice 20181106
pswStr = "CLAVE"
On Error Resume Next
Application.ScreenUpdating = Sai
Mật khẩu ActiveSheet.Unprotect: = pswStr
ActiveSheet.Range ("A4"). Chọn
Phạm vi ("MITABLA [[# Headers], [AVISO 1]]"). Chọn
Selection.End (xlDown) .Select
Selection.Offset (1, -16) .Select
ActiveCell.FormulaR1C1 = "mới"
Mật khẩu ActiveSheet.Protect: = pswStr, DrawingObjects: = False, _
Nội dung: = Đúng, Kịch bản: = Sai, _
AllowFormattingCells: = True, AllowFormattingColumns: = True, _
AllowFormattingRows: = True, AllowInsertingColumns: = True, _
AllowInsertingRows: = True, AllowInsertingHyperlinks: = True, _
AllowDeletingColumns: = True, AllowDeletingRows: = True, _
AllowSorting: = True, AllowFiltering: = True, _
AllowUsingPivotTables: = True
Lựa chọn.ClearContents
Application.ScreenUpdating = True

Lo que está haciendo el código tal cual como lo escribo es que en lugar de agregar una nueva línea a mi tabla, está colocando la palabra "new" en la última celda con Contenido de la columna "AVISO 1".

Surge entonces 2 dudas:
1. ¿cómo podría hacer paraminar más de una columna protegida?
2. ¿por qué está haciendo esto el código Defdo?

Agradezco de antemano que me puedan ayudar! Estaré atenta.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào Daina,
1. Nếu 7 cột công thức mà bạn muốn bảo vệ liên tiếp trong bảng.
Ví dụ: tiêu đề của các cột là gg, hh, ii, jj, kk, ll, mm như thể hiện trong ảnh chụp màn hình bên dưới. Bạn có thể áp dụng mã VBA sau để thực hiện.
https://www.extendoffice.com/images/stories/comments/comment-picture-zxm/table.png
Trong dòng này Đặt xRg = Range ("Table3 [[# Headers], [gg]: [mm]]"). Offset (1, 0) trong đoạn mã sau, bạn chỉ cần nhập tiêu đề của cột đầu tiên và cột cuối cùng.
Sub Button1_Click()
 '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

    'Change the table name and the column headers
    Set tableRg = ActiveSheet.ListObjects("Table3").Range
    xRowCount = tableRg.Rows.Count
    
     Set xRg = Range("Table3[[#Headers],[gg]:[mm]]").Offset(1, 0)
    Set yRg = xRg.Resize(xRowCount, xRg.Columns.Count)

    xRg.Resize(xRowCount - 1, xRg.Columns.Count).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
End Sub

2. Nếu 7 cột công thức mà bạn muốn bảo vệ không liên tục trong bảng. Áp dụng mã sau. Trong mã, bạn cần phải nhập từng tiêu đề của các cột theo cách thủ công.
Sub Button1_Click()
 '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

    'Change the table name and the column headers
    Set tableRg = ActiveSheet.ListObjects("Table3").Range
    xRowCount = tableRg.Rows.Count
    
    Set xRg = Range("Table3[[#Headers],[gg]]").Offset(1, 0)
    Set yRg = xRg.Resize(xRowCount, 1)
    xRg.Resize(xRowCount - 1, 1).AutoFill Destination:=yRg, Type:=xlFillDefault
    Set xRg = Range("Table3[[#Headers],[hh]]").Offset(1, 0)
    Set yRg = xRg.Resize(xRowCount, 1)
    xRg.Resize(xRowCount - 1, 1).AutoFill Destination:=yRg, Type:=xlFillDefault
    Set xRg = Range("Table3[[#Headers],[ii]]").Offset(1, 0)
    Set yRg = xRg.Resize(xRowCount, 1)
    xRg.Resize(xRowCount - 1, 1).AutoFill Destination:=yRg, Type:=xlFillDefault
    Set xRg = Range("Table3[[#Headers],[jj]]").Offset(1, 0)
    Set yRg = xRg.Resize(xRowCount, 1)
    xRg.Resize(xRowCount - 1, 1).AutoFill Destination:=yRg, Type:=xlFillDefault
    Set xRg = Range("Table3[[#Headers],[kk]]").Offset(1, 0)
    Set yRg = xRg.Resize(xRowCount, 1)
    xRg.Resize(xRowCount - 1, 1).AutoFill Destination:=yRg, Type:=xlFillDefault
     Set xRg = Range("Table3[[#Headers],[ll]]").Offset(1, 0)
    Set yRg = xRg.Resize(xRowCount, 1)
    xRg.Resize(xRowCount - 1, 1).AutoFill Destination:=yRg, Type:=xlFillDefault
    Set xRg = Range("Table3[[#Headers],[mm]]").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
End Sub
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Chào.

Cám ơn vì đã chia sẻ. Mặc dù tôi có một câu hỏi ... bằng cách sử dụng mã trên, tôi có thể thêm từng hàng một. Làm thế nào để thêm nhiều hàng trong một cú nhấp chuột?

Cảm ơn trước.

'Cập nhật bởi ExtendOffice 20220826
Làm mờ xRg, tableRg As Range
Dim xRowCount dưới dạng số nguyên
Dim pswStr dưới dạng chuỗi
pswStr = "123"
On Error Resume Next
Application.ScreenUpdating = Sai
Mật khẩu ActiveSheet.Unprotect: = pswStr

Đặt tableRg = ActiveSheet.ListObjects ("Table4"). Phạm vi
xRowCount = tableRg.Rows.Count

Đặt xRg = Range ("Table4 [[# Headers], [Total]]"). Offset (1, 0)
Đặt yRg = xRg.Resize (xRowCount, 1)
xRg.Resize (xRowCount - 1, 1) .AutoFill Destination: = yRg, Type: = xlFillDefault

Mật khẩu ActiveSheet.Protect: = pswStr, DrawingObjects: = False, _
Nội dung: = Đúng, Kịch bản: = Sai, _
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
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Mã không hoạt động.
Một số lỗi.

Dim xRg, tableRg As Range

xRg
là một biến thể không phải là một phạm vi

yRg
không được khai báo gì cả

Set xRg = Range("Table4[[#Headers],[Total]]").Offset(1, 0)

lỗi thời gian chạy 1004
Khi tôi lấy đi TOTAL, nó hoạt động.
Nó không hoạt động với tổng hàng được hiển thị và khi tôi ẩn tổng hàng trong ruy-băng.

Thông thường trang web của bạn thực sự tuyệt vời, nhưng bài viết này cần có sự ứng biến.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Chào tiền,
Bạn cần đảm bảo rằng tên bảng và tiêu đề cột được chỉ định trong mã khớp với tên bảng và tiêu đề cột trong trang tính. Để tránh lỗi 1004, bạn có thể cần bật tin cậy quyền truy cập vào mô hình đối tượng dự án VBA trong Excel của bạn: bấm vào Tập tin > Các lựa chọn > Trung tâm tin tưởng > Cài đặt Trung tâm Niềm tin > Cài đặt Macro > và sau đó kiểm tra Tin cậy quyền truy cập vào mô hình đối tượng dự án VBA cái hộp.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Nút phụOut_Click()

Dim PswS dưới dạng chuỗi
PswStr = "54321"

On Error Resume Next

Application.ScreenUpdating = Sai
ActiveSheet.Mật khẩu không bảo vệ:=PswStr

ActiveSheet.ListObjects("Table1").ListRows.Add

ActiveSheet.Protect Password:=PswStr
Application.ScreenUpdating = True

End Sub
Không có bình luận nào được đăng ở đây
Để lại ý kiến ​​của bạn
Đăng với tư cách khách
×
Đánh giá bài viết này:
0   Nhân vật
Các vị trí được đề xuất

Kết nối với chúng tôi

Bản quyền © 2009 - www.extendoffice.com. | Đã đăng ký Bản quyền. cung cấp bởi ExtendOffice. | BẢN ĐỒ CHI NHÁNH
Microsoft và logo Office là các nhãn hiệu hoặc nhãn hiệu đã đăng ký của Microsoft Corporation tại Hoa Kỳ và / hoặc các quốc gia khác.
Được bảo vệ bởi Sectigo SSL