指定の列と最終データから上の行を空白セルも含めロックしたい

Question

VBA初心者です。初歩的な質問で申し訳ないのですが、教えていただけないでしょうか。
このシートは保護をかけ、特定の人にのみパスワードを教えてシート保護解除できるようにしようと
思っております。

• L列とN列は基本的にロックしておきたい（黄色の列）

　（パスワードを知っている人だけがシート保護解除し入力します）

• リストE列の最終の値（書式ではなく）を認識させて、その上の行を全部ロックしたいです。

　イメージ図で言えば、「E13」が最終値
3行目～13行目（ピンクの行）をロックしたいのですが、
「Columns(12).Locked = True」の所でアプリケーション定義、オブジェクトのエラーで
返されてしまいます。
離れた複数の列を指定してロックしたい時、どのように書けばいいか教えていただけませんか？

Sub マクロ実行()
       Dim maxrow
       Dim 対象範囲 
      
        'E最大セル「E65536」から↑で行番号を取得
       maxrow = Range("E65536").End(xlUp).Row
       
        '3行目以降「maxrow - 1」最終行から1個手前を取得し「対象範囲」に格納
       対象範囲 = Range(Rows(3), Rows(maxrow - 1)).Locked = True
       
              
       'N列,P列をロックする
       Columns(12).Locked = True
       Columns(14).Locked = True
    ActiveSheet.Protect
End Sub
コード

Answer 1

セルは基本「ロックされている状態」が基本です。
なので一旦、シート上の全てのセルを初期化して、
改めて入力していいセルだけ解除するように考えてはいかがでしょうか？

Sheet1のモジュールに以下のコードをコピペ

Private Sub Worksheet_Change(ByVal Target As Range)
    Me.Unprotect
    Me.Cells.Locked = True
    With Me.Range("A3").CurrentRegion
        Intersect(Me.Range("B:K"), .Rows(.Rows.Count + 1)).Locked = False
        .Columns(1).Offset(1).DataSeries
    End With
    Me.Protect
End Sub

こんな感じでセルに入力したら、ロックする位置を変えたらいいと思います。
が、
入力ミスがあった時に、編集が出来なくなるので、
ロックの位置を変えるのは、
ブックを閉じる「とき」か、
ブックを開いた「とき」に判定したらいいかもです。

参考URL>>

Excel(エクセル) VBA入門：ワークシートのイベント

第124回.Workbookのイベントプロシージャー

    'E最大セル「E65536」から↑で行番号を取得
   maxrow = Range("E65536").End(xlUp).Row

それから、エクセルのシートの最大行数は、65536行目ではありません。
それは、2003以前の行数です。

Worksheets(1).rows.count

というようにして、行の数を数えましょう。

Comments

[green88008800]

回答していただきありがとうございます。
すごい！逆転の発想ですね。思いつきもせず、ロックすることのみ考えてました。
早速、試しているところです。
Excelシートの最大行数を誤って覚えていたとは・・・
2003以前だったとは・・・お恥ずかしい限りです。
古いサイトを見て65536行目だと信じていました。
教えていただきありがとうございます。

[mattuwan]

2007以前かもしれません＾＾；いずれにしても、今後仕様がまた変わってもいいように書くなら、
その都度、該当シートの行数を数える方が無難でしょう。
開いているブックが、いろいろなバージョンのブックが混在している場合に定数で指定したり、ブックやシートの指定を省略したりする癖がついていると、後々、対応が難しいかもです。

[green88008800]

Excelはどんどん進化してるんですね～驚きです。
「Worksheets(1).rows.count」を教えていただいたので、活用します。
ありがとうございます。

Answer 2

object.lockedはRange又はCellsオブジェクトしかサポートしていません。
誤　Columns(n).locked = True
正　Range("L:L,N:N").locked = True

あと、エクセルは標準ではセル全体がlocked=Trueです。
もし、L、N列、使用済みの行(最終値除く)以外は編集可能にしたいのであれば、下記のような感じになります。

余計なお世話かもしれませんが、maxrowの取得はE列だけだと他のデータ列にデータが入力された時に対応ができないので、usedrangeやspecialcellsを使うことをおすすめします。

Sub マクロ実行()
       Dim maxrow
       Dim 対象範囲 

        'シート内で使用されているセル範囲の最終行を取得
       maxrow = Activesheet.UsedRange.Row

　　　　' セル全体をロック解除
       Cells.Locked = False

        '3行目以降「maxrow - 1」最終行から1個手前を取得し「対象範囲」に格納
       対象範囲 = Range(Rows(3), Rows(maxrow - 1)).Locked = True


       'L列,N列をロックする
       Range("L:L, N:N").Locked = True
    ActiveSheet.Protect
End Sub

Comments

[green88008800]

回答していただきありがとうございます。
maxrowの取得,E列だけだと他の列にデータが入った時対応できないこと、気づきませんでした。教えていただきありがとうございます。
マクロ実行した時、
対象範囲 = Range(Rows(3), Rows(maxrow - 1)).Locked = True
の所で｢1004 アプリケーション定義またはオブジェクト定義のエラー｣がでるのですが、これはアクティブになっていないせいでしょうか？
対象範囲 = Range(Rows(3), Rows(maxrow - 1)).Locked = True
をアクティブにしようと色々試したのですが、うまくいかず悩んでいます。
もし、良ければ教えていただけませんか？

[ELBE]

lockedメソッドは戻り値として、Boolean型を返します。
しかし、locked = TrueなどlockedメソッドにBoolean型を指定した時、変更が成功すれば何も戻り値を返しません。(正確には、nullではなく例外を投げています)

そのため、対象範囲という変数に対して、何も代入されなかったためエラーが起きています。

対象範囲 = を消して、下記の式にすると良いと思います。
Range(Rows(3), Rows(maxrow - 1)).Locked = True

[ELBE]

補足:
対象範囲としてロックしたセル範囲を変数上に確保しておきたい。のであれば、対象範囲という変数にRangeオブジェクトとして格納し、それに対してlockedメソッドを行うと良いかと。

対象範囲 = Range(Rows(3), Rows(maxrow - 1))
対象範囲.Locked = True

[green88008800]

回答していただきありがとうございます。
なるほど、何も代入されないためにエラーが起きているのですね。
早速以下のコードでやってみたのですが、
「Range(Rows(3), Rows(maxrow - 1)).Locked = True」の
ところで「実行時エラー1004、アプリケーション定義、オブジェクト定義のエラー」がでてしまいます。
Sub マクロ実行()
Dim maxrow
maxrow = ActiveSheet.UsedRange.Row
Cells.Locked = False
Range(Rows(3), Rows(maxrow - 1)).Locked = True
　　 'エラー1004　アプリケーション定義、オブジェクトエラー
Range("L:L, N:N").Locked = True
ActiveSheet.Protect
End Sub

そこで、
Sub マクロ実行()
Dim maxrow
Dim 対象範囲
maxrow = ActiveSheet.UsedRange.Row
Cells.Locked = False
対象範囲 = Range(Rows(3), Rows(maxrow - 1))
対象範囲.Locked = True 'オブジェクトが必要です。実行時エラー424
Range("L:L, N:N").Locked = True
ActiveSheet.Protect
End Sub
でやってみたのですが、「対象範囲.Locked = True」のところで
'オブジェクトが必要です。実行時エラー424がでます。
初心者で勉強不足でやり方が間違っているのかもしれないのですが、
エラーが出る理由が分からなくて。どのように直すべきでしょうか？
Setで格納されていないせいでしょうか。