none
Excel シートで行の挿入・削除のみを許可して、シートを保護するには? RRS feed

  • 質問

  • お世話になります。芦田と申します。Excel VBA での質問です。

    複数のワークシートがあるブックの中で、
    A.シート上の特定のセルは入力可能(ロックなし)、それ以外のセルは入力不可(ロック状態)にします。
    B.だだし、そのシートではユーザーに行の挿入・削除を許可したいと思っています。

    A.については Protect で実現しているのですが、B.がうまくできません。

    対象となる Excel のバージョンは 2013, 2016(いずれも 32-bit 版)です。 

    このようなことをするにはどのようなコードを書けばよいのでしょうか?
    識者の方々、どうかお知恵をお貸しください。


    • 編集済み Ashidacchi 2016年6月27日 8:41 補足説明を追加
    2016年6月27日 8:32

回答

  • こんにちは。

    > これはマクロでは無理ということでしょうか?

    マクロの問題というよりは、ロックされたセルが含まれていると行の削除を許可していてもその行は削除することができませんので、Excelの仕様上難しいのではないでしょうか。
    (もちろん、ユーザーに行の挿入・削除を許可する行全体のロックを外していれば可能です。)

    「範囲の編集を許可」あたりで何とかなるかもしれませんが、私自身あまり保護機能を使わないため、詳しくは知りません。

    > 行の挿入/削除を許可する範囲が一つのシートに複数(3か所)あり、[挿入][削除]のボタンをそれぞれ3か所に用意しないと使い勝手が悪いかなと思い、アイデアだけに終わっています(実装していません)

    マクロをどう呼び出すかが問題なのであれば、ボタンを3つ用意する必要はなく、右クリックメニューやリボンへの追加で対応可能だと思います。

    たとえばセルを右クリックした際のメニューにマクロを呼び出すコントロールを追加する場合は、下記のような処理で実現可能です。

    '標準モジュール
    Option Explicit
    
    Private Const InsCtrlCap As String = "行の追加(&X)"
    Private Const DelCtrlCap As String = "行の削除(&Y)"
    Private Const ProtectPW As String = "pass"
    
    Private Sub Auto_Open()
    'ファイルを開いたときに実行
      AddCellControls
    End Sub
    
    Private Sub Auto_Close()
    'ファイルを閉じるときに実行
      DeleteCellControls
    End Sub
    
    Private Sub InsertRows()
    '行の追加
      On Error Resume Next
      ActiveSheet.Unprotect ProtectPW
      Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
      ActiveSheet.Protect Password:=ProtectPW
    End Sub
    
    Private Sub DeleteRows()
    '行の削除
      On Error Resume Next
      ActiveSheet.Unprotect ProtectPW
      Selection.Delete Shift:=xlUp
      ActiveSheet.Protect Password:=ProtectPW
    End Sub
    
    Private Sub AddCellControls()
    'セルの右クリックメニューにコントロール追加
      DeleteCellControls '念のため事前に削除
      With Application.CommandBars("Cell").Controls
        '行の追加ボタン追加
        With .Add(Type:=msoControlButton, Temporary:=True)
          .Caption = InsCtrlCap
          .OnAction = "InsertRows"
          .FaceId = 295
        End With
        '行の削除ボタン追加
        With .Add(Type:=msoControlButton, Temporary:=True)
          .Caption = DelCtrlCap
          .OnAction = "DeleteRows"
          .FaceId = 292
        End With
      End With
    End Sub
    
    Private Sub DeleteCellControls()
    'セルの右クリックメニューからコントロール削除
      On Error Resume Next
      With Application.CommandBars("Cell").Controls
        .Item(InsCtrlCap).Delete
        .Item(DelCtrlCap).Delete
      End With
    End Sub

    行番号や列名、名前付きセル範囲等で、処理を実行する範囲を限定することもできます。
    • 回答としてマーク Ashidacchi 2016年7月3日 0:06
    2016年6月30日 1:35

すべての返信

  • こんにちは。

    行の挿入・削除をExcelの標準機能ではなく、マクロで行うようにするのはいかがでしょうか?
    (マクロはボタンなり右クリックメニューなりに追加)

    Unprotectメソッドで保護解除

    行の挿入・削除

    Protectメソッドで再度保護
    2016年6月29日 7:05
  • きぬあさ様、リプライをいただき、ありがとうございます。

    行の[挿入][削除]のためのボタンを用意して、
     1.選択されている行の下に(指定された行数の)行を挿入する
     2.選択されている行(複数行の選択可)を削除する
    ことを考えてはみたのですが・・・

    行の挿入/削除を許可する範囲が一つのシートに複数(3か所)あり、[挿入][削除]のボタンをそれぞれ3か所に用意しないと使い勝手が悪いかなと思い、アイデアだけに終わっています(実装していません)。

    最初の質問で書いた要件は次のとおりでした。
      A.シート上の特定のセルは入力可能(ロックなし)、それ以外のセルは入力不可(ロック状態)にします。
      B.だだし、そのシートではユーザーに行の挿入・削除を許可したいと思っています。
      A.については Protect で実現しているのですが、B.がうまくできません。

    これはマクロでは無理ということでしょうか?
    もし、無理であるなら、[挿入][削除]ボタンのアイデアを実装します。

    2016年6月29日 8:35
  • こんにちは。

    > これはマクロでは無理ということでしょうか?

    マクロの問題というよりは、ロックされたセルが含まれていると行の削除を許可していてもその行は削除することができませんので、Excelの仕様上難しいのではないでしょうか。
    (もちろん、ユーザーに行の挿入・削除を許可する行全体のロックを外していれば可能です。)

    「範囲の編集を許可」あたりで何とかなるかもしれませんが、私自身あまり保護機能を使わないため、詳しくは知りません。

    > 行の挿入/削除を許可する範囲が一つのシートに複数(3か所)あり、[挿入][削除]のボタンをそれぞれ3か所に用意しないと使い勝手が悪いかなと思い、アイデアだけに終わっています(実装していません)

    マクロをどう呼び出すかが問題なのであれば、ボタンを3つ用意する必要はなく、右クリックメニューやリボンへの追加で対応可能だと思います。

    たとえばセルを右クリックした際のメニューにマクロを呼び出すコントロールを追加する場合は、下記のような処理で実現可能です。

    '標準モジュール
    Option Explicit
    
    Private Const InsCtrlCap As String = "行の追加(&X)"
    Private Const DelCtrlCap As String = "行の削除(&Y)"
    Private Const ProtectPW As String = "pass"
    
    Private Sub Auto_Open()
    'ファイルを開いたときに実行
      AddCellControls
    End Sub
    
    Private Sub Auto_Close()
    'ファイルを閉じるときに実行
      DeleteCellControls
    End Sub
    
    Private Sub InsertRows()
    '行の追加
      On Error Resume Next
      ActiveSheet.Unprotect ProtectPW
      Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
      ActiveSheet.Protect Password:=ProtectPW
    End Sub
    
    Private Sub DeleteRows()
    '行の削除
      On Error Resume Next
      ActiveSheet.Unprotect ProtectPW
      Selection.Delete Shift:=xlUp
      ActiveSheet.Protect Password:=ProtectPW
    End Sub
    
    Private Sub AddCellControls()
    'セルの右クリックメニューにコントロール追加
      DeleteCellControls '念のため事前に削除
      With Application.CommandBars("Cell").Controls
        '行の追加ボタン追加
        With .Add(Type:=msoControlButton, Temporary:=True)
          .Caption = InsCtrlCap
          .OnAction = "InsertRows"
          .FaceId = 295
        End With
        '行の削除ボタン追加
        With .Add(Type:=msoControlButton, Temporary:=True)
          .Caption = DelCtrlCap
          .OnAction = "DeleteRows"
          .FaceId = 292
        End With
      End With
    End Sub
    
    Private Sub DeleteCellControls()
    'セルの右クリックメニューからコントロール削除
      On Error Resume Next
      With Application.CommandBars("Cell").Controls
        .Item(InsCtrlCap).Delete
        .Item(DelCtrlCap).Delete
      End With
    End Sub

    行番号や列名、名前付きセル範囲等で、処理を実行する範囲を限定することもできます。
    • 回答としてマーク Ashidacchi 2016年7月3日 0:06
    2016年6月30日 1:35
  • きぬあさ様、コードまでご提示いただき、ありがとうございます。

    今日は他用があり試すことができないので、明日以降にご提示いただいたコードを参考にさせていただき試してみます。
    取り急ぎ、お礼まで。
    2016年6月30日 8:13
  • きぬあさ様、

    ご提示いただいたコードをほんのわずか手直しするだけで、思っていたことが実現できました。
    右クリック動作なので、ボタンを複数用意する必要もなく(ボタン位置が変化するのを気にする必要もなく)、ユーザーには使い勝手がよく、かつ私には開発/修正が容易な処理ができました。

    お礼申し上げます。どうもありがとうございました。 <m(__)m>  芦田

    【追記】 コードの手直し:
     「行」の挿入/削除を行いたかったので、Rows.Insert / Rows.Delete にししました。
     Selection.Insert / Selection.Delete ではセル(列)の挿入/削除の動作となったためです。
    ' --- 行の追加 ---
    Private Sub InsertRow()
        On Error Resume Next
        ActiveSheet.Unprotect Password:=myModule.myPassword ' -- シート保護を解除
        ' Selection.Insert Shift:=xlDown  ', CopyOrigin:=xlFormatFromLeftOrAbove
        Rows(Selection.Row).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        ActiveSheet.Protect Password:=myModule.myPassword ' -- シートを保護
    End Sub
    ' --- 行の削除 ---
    Private Sub DeleteRow()
        On Error Resume Next
        ActiveSheet.Unprotect Password:=myModule.myPassword ' -- シート保護を解除
        ' Selection.Delete Shift:=xlUp
        Rows(Selection.Row).Delete Shift:=xlUp
        ActiveSheet.Protect Password:=myModule.myPassword ' -- シートを保護
    End Sub
    

    2016年7月3日 0:06