the code worked initially, but after I duplicated the worksheet, it stayed for after 24 hours then all the code disappeared. And now I can’t access the worksheet.
it keeps telling me incorrect password. And the code have disappeared. .
Kutools for Excel is a powerful add-in that frees you from performing time-consuming operations in Excel, such as combine sheets quickly, merge cells without losing data, paste to only visible cells, count cells by color and so on. 300+ powerful features / functions for Excel 2019, 2016, 2013, 2010, 2007 or Office 365!
It enables tabbed browsing, editing, and managing of Microsoft Office applications. You can open multiple documents / files in a single tabbed window, such as using the browser IE 8/9/10, Firefox, and Google Chrome. It's compatible with Office 2019, 2016, 2013, 2010, 2007, 2003 or Office 365. Demo
Kutools for Outlook is a powerful add-in that frees you from time-consuming operations which majority of Outlook users has to perform daily! It can save your time from using Microsoft Outlook 2019, 2016, 2013, 2010 or Office 365!
Kutools for Word is a powerful add-in that frees you from time-consuming operations which majority of Word users have to perform daily! It can save your time from using Microsoft Word / Office 2019, 2016, 2013, 2010, 2007, 2003 or Office 365!
Restores the old look and menus of Office 2003 to Microsoft Office 2019, 2016, 2013, 2010, 2007 or Office 365. Don’t lose time in finding commands on the new Ribbon. Easy to deploy to all computers in enterprises and organizations.
The auto expanding function of table will be lost after protecting the worksheet in Excel. For example, there is a table named Table1 in your protected worksheet, when you type anything under the last row, the table will not automatically expand to include the new row. Is there method to keep table expandable by inserting new row in a protected worksheet? The method in this article can help you to achieve it.
As below screenshot shown, a table named Table1 in your worksheet, and the last column of the table is a formula column. Now you need to protect the worksheet to prevent the formula column from changing, but allow to expand the table by inserting new row and assign new data into the new cells. Please do as follows.
1. Click Developer > Insert > Button (Form Control) to insert a Form Control button into your worksheet.
2. In the popping up Assign Macro dialog box, click the New button.
3. In the Microsoft Visual Basic for Applications window, please copy and paste the below VBA code between the Sub and End Sub paragraphs in the Code window.
VBA code: Keep table expandable by inserting table row in a protected worksheet
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). In the code, number “123” is the password you will use to protect the worksheet.
2). Please change the table name and the name of the column that is containing formula you will protect.
3). And change the offset in the code Selection.Offset(1, -4).Select to the first target column of the table. For example, if there are six columns in the table, please change the Offset to (1, -5).
4. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.
5. Select the cells in the table you need to assign new data into except the formula column, then press the Ctrl + 1 keys to open the Format Cells dialog box. In the Format Cells dialog box, uncheck the Locked box, and then click the OK button. See screenshot:
6. Now protect your worksheet with password you have specified in the VBA code.
From now on, after clicking the Form Control button in your protected worksheet, the table will be expandable by inserting new row as below screenshot shown.
Note: you can modify the table except the formula column in the protected worksheet.