VBA MsgBOXでの処理分岐

質問

B列に抹消と入力したら別の列に抹消と記載する以下のマクロがありますが、
B列に抹消と入力したらMsgBoxで抹消しますか？と表示させたいです。

MsgBox関数を特定場所に挿入したのですが、うまくいかず、どこに挿入したらよろしいでしょうか。（オブジェクトが必要です。のエラーが出てしまう状況です。）

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim h As Range

On Error Resume Next
For Each h In Application.Intersect(Target, Range("B:B"))
If h = "抹消" Then

Cells(h.Row, "E").Resize(1, 7).SpecialCells(xlCellTypeConstants) = "抹消"

End If
Next

End Sub

回答No.1

MarcoRossiItaly · ベストアンサー

つまらぬ心配ですが、「抹消」と「末梢」とか、打ち間違えないようご注意。私は間違えました(笑) リストの機能で B 列の値を制限でもしておけば、それは回避できそうですね。

MsgBox 関数を入れる場合、On Error Resume Next のままにしておくと B 列以外の変更であっても何でもかんでもメッセージが出てしまうので、On Error Resume Next は削除します。元々、一般的には、変更するセル範囲を絞ってプロシージャを実行する場合、On Error Resume Next により力ずくで動かすよりも、Intersect メソッドで場合分けをします。

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
　　Dim h As Range
　　If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
　　For Each h In Intersect(Target, Range("B:B"))
　　　　If h.Value = "抹消" Then
　　　　　　If MsgBox(prompt:="抹消しますか？", Buttons:=vbOKCancel) = vbOK Then
　　　　　　　　Cells(h.Row, "E").Resize(1, 7).SpecialCells(xlCellTypeConstants).Value = "抹消"
　　　　　　End If
　　　　End If
　　Next h
End Sub

お礼

ありがとうございました。とても勉強になりました。