まりふのひと

Excel VBA/シートの保護がやっと完成した‥‥かも

 ある会社の運行管理者? の方から Excelに関して相談を受けたのがきっかけで、Excel VBAの虫が動き出した。

 ある日、「右端(T列)のデータ入力後、B列にカーソルが移動できないか?」と相談(命令?)を受けた。家計簿77 で経験があるので「やりましょう!」と...

手動での操作

  1. B~T列を選択する。
  2. ホーム タブ/セル グループの[書式]⇒ セルのロック をオフにする。(既定ではセルのロックはオンになっている)
  3. 更に、[書式]⇒ シートの保護 を選択し、
  4. 右図のように設定し、[OK]する。f:id:ogohnohito:20200612084649p:plain:right:w240
    • ☑ロックされたセル範囲の選択 のチェックを外す。
       ∵外さないと、T列で Enter するとカーソルはU列に移動してしまうため。
    • ☐セルの書式設定,☐列の書式設定にチェックを入れるのは、トラックNo.のD列はフォントの色を変えるため。
    • ☐行の書式設定 以下は、既定でチェックは入っていない‥‥ はず。

上記設定で、Enter でカーソルはB~T列間を移動するようになる。

VBAの作成

 [マクロの記録]で作成すると、作成後の修正が面倒なので、マクロ家計簿77.xlsm の該当部分のコードをコピペした。と、ところが動作検証したら「おかしい」ケースがある(シートによっては、カーソルがU列に移動する)...
これって「ヤバい!」のでは? 本番で動いているコードにバグがある?‥‥ と慌てたが、Stay Home では入れ替わり立ち代わり体中が痒くなるので集中できない... 見て見ぬ振りをするしかなかった。
 体調が回復してきたので、まずは今の課題の解決するため Google先生に聞いた。

  • VBA シートを保護する(引用:Tipsfound)
    • 非常に参考になるページであるが、「ロックされたセル範囲の選択」に関するのが何処か‥‥ わからない...
  • ユーザー操作を制限する(Locked,Protect,ScrollArea)(引用:エクセルの神髄 最終更新日:2014-11-07)
     初めて保護設定するシートの場合は、以下の「シートの保護」のダイアログで初期選択されているオプションが選択された状態となります。
    この状態ですと、保護したあるセルも選択(クリック)出来てしまいます。クリックも許可しない場合は、上の「シートの保護」のダイアログの一番上の「ロックされたセル範囲の選択」このチェックを外します、それは、
      シート.EnableSelection = xlUnlockedCells
    と記述
    します。

 Excelの神髄さん 有難うございます。当面の問題が解決出来ました。

完成したVBA

01. Sub シートの保護()
02. On Error GoTo Err_シートの保護
03. Dim mbTitle As String
04. Dim rowTitle As Long
05. Dim lastCol As Long, lastRow As Long
06. Dim lastColA As String
07.     
08.     mbTitle = "シートの保護/" & getMBTitle()
09.         '入力列の範囲(最終列)をセットする。
10.     rowTitle = getタイトル行番号()
11.     If rowTitle = 0 Then Exit Sub
12.         'セルのロックを解除する。
13.     lastCol = Cells(rowTitle, Columns.Count).End(xlToLeft).Column
14.     Range(Columns(2), Columns(lastCol)).Select
15.     With Selection
16.         .Locked = False
17.         .FormulaHidden = False
18.     End With
19.         'セルを保護する。
20.     ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
21.             , AllowFormattingCells:=True _
22.             , AllowFormattingColumns:=True
23.         '「ロックされたセル範囲の選択」のチェックを外すには下記が必要‥‥ のようだ。
24.     ActiveSheet.EnableSelection = xlUnlockedCells
25.         '終了時
26.     Cells(getLast行番号() + 1, 2).Select
27.     Exit Sub
28.     
29. Err_シートの保護:
30.     MsgBox "実行時エラーが発生しました。" & vbCrLf _
31.             & " ・エラーコード:" & Err.Number & vbCrLf _
32.             & " ・メッセージ:" & Err.Description & vbCrLf & vbCrLf _
33.             & "シートが保護されている可能性があります。解除後、再実行してみてください。" _
34.             , vbExclamation, mbTitle
35.         Resume Exit_シートの保護
36.         
37. Exit_シートの保護:
38.     Exit Sub
39.     
40. End Sub