ActiveCellを使うと想定外の動きをする件【Excel】【VBA】

投稿日：2020年4月10日更新日：2022年11月23日

VBAでActiveCellを使ったコードを書いた際に、少し躓いたため今回記事にします。

まずは、シート上を以下のように準備します。
A1セルはリスト化し、「1」「2」「3」が選択できる状態にします。

続いて操作したセルを黄色くするように以下のソースコードを準備します。


'Sheet1
Private Sub Worksheet_Change(ByVal target As Range)         'ワークシート上で値が変更されると実行

    Call 色を変える                                         '色を変えるプロシージャの呼び出し

End Sub


'Module1
Public Sub 色を変える()

    ActiveCell.Interior.Color = RGB(255, 255, 0)    'セルの背景色を黄色くする

End Sub

シート上のセルで値入力の操作が行われると、自動的にWorksheet_Changeプロシージャが呼び出されます。
Worksheet_Changeプロシージャから色を変えるプロシージャが呼び出され、Activeセルの背景色が黄色くなります。
A1セルをリストで入力した場合はA1セルの背景色が黄色くなることが分かります。

しかし、A1セルを手入力で編集し、Enterキーを押すとActiveセルがA2セルとなるため、先ほどと違ってA2セルの背景色が黄色くなってしまいました。

A1セルの編集内容が同じであるにも関わらず、動作結果が異なってしまうため、ソースコードを改善する必要があります。

そこでソースコードを以下のように改善します。


'Sheet2
Private Sub Worksheet_Change(ByVal target As Range)         'ワークシート上で値が変更されると実行

    Call 色を変える2(target)                                '色を変えるプロシージャの呼び出し

End Sub


'Module2
Public Sub 色を変える2(target)

    Cells(target.Row, target.Column).Interior.Color = RGB(255, 255, 0)   'セルの背景色を黄色くする

End Sub

今度は操作するセルの位置情報を「target」として、色を変える2プロシージャ呼び出しの際の引数としています。背景色を黄色くする処理もActiveセルではなく、targetのセル位置を指定しています。

このソースコードでA1セルをリストで入力します。

A1セルが黄色くなっていることが分かります。

続いてA1セルを手入力で編集し、Enterキーを押してみます。

一つ目のソースコードと異なり、A1セルが黄色くなったことが確認できます。これにより、リスト入力と手入力の両方で動作結果が同じとなりました。