保護されたワークシートでテーブルを展開可能に保つには、Excelでどのように行を挿入すればよいですか？

Excelでは、ワークシートを保護すると、テーブルの自動拡張機能が無効になることがよくあります。たとえば、Table1という名前のテーブルがあり、保護されたワークシートの最後の行の下にデータを追加しようとすると、そのテーブルは新しい行を含むように自動的に拡張されません。しかし、保護されたワークシートであってもテーブルを展開可能に保つ方法があります。このチュートリアルでは、ワークシートが保護された状態でテーブルの自動拡張を有効にする手順を説明します。

VBAコードを使用して、保護されたワークシートに行を挿入することでテーブルを展開可能に保つ

VBAコードを使用して、保護されたワークシートに行を挿入することでテーブルを展開可能に保つ

以下のスクリーンショットに示すように、ワークシート内にTable1という名前のテーブルがあり、テーブルの最後の列は数式列です。ここで、数式列が変更されないようにワークシートを保護しつつ、新しい行を挿入してテーブルを拡張し、新しいセルにデータを割り当てられるようにする必要があります。次の手順に従ってください。

1. 「開発」＞「挿入」＞「ボタン（フォーム コントロール）」の順にクリックして、フォーム コントロール ボタンをワークシートに挿入します。

2. 表示される「マクロの割り当て」ダイアログ ボックスで、「新規」ボタンをクリックします。

3. 「Microsoft Visual Basic for Applications」ウィンドウで、以下の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 for Applications」ウィンドウを閉じます。

5. 数式列以外の、新しいデータを割り当てる必要があるテーブル内のセルを選択し、Ctrl + 1 キーを押して「セルの書式設定」ダイアログ ボックスを開きます。「セルの書式設定」ダイアログ ボックスで、「ロック」チェック ボックスのチェックを外し、「OK」ボタンをクリックします。スクリーンショットをご覧ください：

6. VBAコードで指定したパスワードを使用してワークシートを保護します。

これ以降、保護されたワークシートのフォーム コントロール ボタンをクリックすると、以下のスクリーンショットに示すように、新しい行を挿入してテーブルが展開可能になります。

注意: 保護されたワークシートの数式列以外の部分は、テーブルを変更できます。

関連記事：

• Excelでピボットテーブルのスライサーをワークシートのスクロールに合わせて移動させるにはどうすればよいですか？
• Excelでセルを結合しつつ、セルの書式を保持するにはどうすればよいですか？
• Excelで重複を削除しつつ最初のインスタンスを保持するにはどうすればよいですか？