実行時エラー１００４の回避方法

Question

前提・実現したいこと

エクセルでＡ列のセルに入力をすると、Ｂ列に入力した日時が表示（Ａ１に入力するとＢ１に日時が表示）するようにＶＢＡでコードを入れてあります。
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim r As Range
For Each r In Target
If r.Column = 1 Then
r.Offset(0, 1).Value = Format(Now, "mm月dd日(aaa）hh:mm")
End If
Next r
End Sub
またＡ列のセルに入力後、Ｂ列に表示されたセルの日時が変更されないように、Ｂ列のセルをロックして、シートの保護をしました。

発生している問題・エラーメッセージ

シートの保護をしている状態でＡ１セルに入力すると、実行時エラー１００４　アプリケーション定義またはオブジェクト定義のエラーですと表示されます。

該当のソースコード

デバックを表示すると
r.Offset(0, 1).Value = Format(Now, "mm月dd日(aaa）hh:mm")
の部分が黄色に表示されています

試したこと

ネットでＶＢＡとシートの保護の関係をいろいろ調べましたが、どのようにコードを改善すればいいのか分かりません。上記のコードもネットで調べて作成したもので、私自身ＶＢＡの知識があさいため、解決策が見つからない状態です。解決策をご教授いただけるとたいへんありがたいです。宜しくお願い致します。

補足情報（FW/ツールのバージョンなど）

ここにより詳細な情報を記載してください。打ち消し線

Comments

[m.ts10806]

コードはマークダウンのcode機能を利用してご提示ください。

[VBABeginner1]

大変わかりやすい方法をご提示いただきありがとうございました。
無事に解決できました。これからもっとＶＢＡを勉強していきたいと思います。

[m.ts10806]

いえ、これは私へではなく回答者へ、では。
私のは質問の編集依頼です。質問は編集できます

Answer 1

シートの保護がかかっている時点で仕方が無い、という現象ですが、1点だけ突破法が。

Excelの画面上から保護をかけると、人間が使った場合もVBAが処理した場合も書き換え不能になりますが、プログラム内で保護設定すると「人間が使うときだけ保護」という設定ができるようです。

VBAの「ThisWorkbook」のところをダブルクリックして、コード画面で

Private Sub Workbook_Open()
    Worksheets("Sheet1").Protect Password:="", UserInterfaceOnly:=True
End Sub

のようなコードを追加してみてください。「Sheet1」に保護を改めてかけているのですが、「UserInterfaceOnly」という設定をつけることで、人間が使う場合だけ保護して、プログラムからは保護しないようになります。

Comments

[VBABeginner1]

ご回答ありがとうございました。こちらの方法でも対応可能を確認できました。
いろんな解決方法を学べてたいへん光栄です。

Answer 2

シートの保護を解除してから更新すればよろしいかと。

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim r As Range
    Me.Unprotect ' 保護解除
    For Each r In Target
        If r.Column = 1 Then
            r.Offset(0, 1).Value = Format(Now, "mm月dd日(aaa）hh:mm")
        End If
    Next r
    Me.Protect ' 保護
End Sub

Comments

[VBABeginner1]

大変わかりやすい方法をご提示いただきありがとうございました。
無事に解決できました。これからもっとＶＢＡを勉強していきたいと思います。