【ExcelVBA】セルの値の条件合致・相違に合わせてフォームコントロールのチェックボックスのチェック付与・解除を行う方法

この記事では、セル値の条件合致・相違に合わせてフォームコントロールのチェックボックスのチェック付与・解除を行う方法についてご説明します。

【動画】セル値の条件合致・相違に合わせてフォームコントロールのチェックボックスのチェック付与・解除を行っている実際の動き

本題に入る前に、まずは次の動画をご覧ください。

フォームコントロールのチェックボックスに対して、セルの値が条件を満たしている場合はチェックを付け、セルの値が条件を満たしていない場合はチェックを外しています。(またはチェックしない)

動画内の「セルの値の条件」とは、入力された値が51以上100以下の値かどうかです。

51以上100以下の値が入力されればチェックを付け、それ以外の値ならチェックを外す、もしくはチェックしません。

セル値の条件合致・相違に合わせてフォームコントロールのチェックボックスのチェック付与・解除を行う方法

セル値の条件合致・相違に合わせてフォームコントロールのチェックボックスのチェック付与・解除を行うには、次の流れの通りに行います。

作業の流れ

【STEP.1】フォームコントロールのチェックボックスをシートに設置する

フォームコントロールのチェックボックスをシートに設置します。

なお、今回はフォームコントロールのチェックボックスを5つ設置しています。

【STEP.2】フォームコントロールのチェックボックスに名前を付ける

設置したフォームコントロールのチェックボックスに名前を付けます。

今回は5つのフォームコントロールのチェックボックスに次の通り名前を付けています。

コードの例

Excelのマクロのコード(例)

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range, rng2  As Range        'Rangeオブジェクト格納用変数
    Dim adrs                As String       'セルのアドレス位置
    Dim adrsAry()           As Variant      'セルのアドレス位置を格納する配列
    Dim cbxNM               As String       'チェックボックスの名前を格納する配列
    Dim cnt                 As Integer      'カウンタ用変数
        
    'セルの範囲を取得する
    Set rng = Sheets("top").Range("C3:C7")
    
    For Each rng2 In rng
    
        ReDim Preserve adrsAry(cnt)
    
        '配列にデータを格納する
        adrsAry(cnt) = "C" & rng2.Row
    
        cnt = cnt + 1
    
    Next rng2
    
    If Replace$(Target.Cells(1).Address(0, 0), Target.Cells(1).Row, "") <> "C" Then
    
        'C列以外のセルを編集した場合
        
        '本処理を終了する
        Exit Sub
        
    End If
    
    For Each rng In ActiveWindow.RangeSelection
    
        If ActiveWindow.RangeSelection.Count = 1 Then
            
            '選択されているセルが1つの場合
        
            If Target.Row = rng.Row Then
                
                '値が変わった後に選択されている行位置と直前に選択されていたセルの行位置が一緒の場合
                
                '値が変更されたセルの位置を取得する
                adrs = "C" & rng.Row
                
                'チェックボックスのチェック付与・解除処理用関数を呼び出す
                Call cbx_checkONOFF(cbxNM, adrs, adrsAry)
                
            Else
            
                '値が変わった後に選択されている行位置と直前に選択されていたセルの行位置が異なる場合
            
                '値が変更されたセルの位置を取得する
                adrs = "C" & Target.Row
                
                'チェックボックスのチェック付与・解除処理用関数を呼び出す
                Call cbx_checkONOFF(cbxNM, adrs, adrsAry)
                
            End If
            
        Else
        
            '選択されているセルが2つ以上の場合
    
            '値が変更されたセルの位置を取得する
            adrs = "C" & rng.Row
            
            'チェックボックスのチェック付与・解除処理用関数を呼び出す
            Call cbx_checkONOFF(cbxNM, adrs, adrsAry)
            
        End If
    
    Next

End Sub

Sub cbx_checkONOFF(cbxNM As String, _
                   adrs As String, _
                   adrsAry() As Variant)

    Dim cbxNMAry()          As Variant      'チェックボックスの名前を格納する配列
    
    'チェックボックス名を取得
    cbxNMAry = Array("cbx_1", _
                     "cbx_2", _
                     "cbx_3", _
                     "cbx_4", _
                     "cbx_5")
    
    'セルの位置が配列に含まれているか判定
    If IsError(Application.Match(adrs, adrsAry, 0)) = False Then
    
        'チェックボックス名を取得する
        cbxNM = cbxNMAry(WorksheetFunction.Match(adrs, adrsAry, 0) - 1)
    
        If Sheets("top").Range(adrs).Value >= 51 And _
           Sheets("top").Range(adrs).Value <= 100 Then
    
            '値が51以上100以下の場合
    
            'チェックボックスのチェックを付ける
            Sheets("top").CheckBoxes(cbxNM).Value = xlOn
    
        Else
    
            'チェックボックスのチェックを外す
            Sheets("top").CheckBoxes(cbxNM).Value = xlOff
    
        End If
    
    End If

End Sub

注目すべきコード①

最初に見て頂きたいのは12行目から23行目です。

    'セルの範囲を取得する
    Set rng = Sheets("top").Range("C3:C7")
    
    For Each rng2 In rng
    
        ReDim Preserve adrsAry(cnt)
    
        '配列にデータを格納する
        adrsAry(cnt) = "C" & rng2.Row
    
        cnt = cnt + 1
    
    Next rng2

12行目でセルの範囲(C3からC7のセル)を取得し、19行目で配列「adrsAry」に格納しています。

この配列「adrsAry」は、チェックボックスのチェックを付けるか外すかの処理で使います。(どのセルの左隣のチェックボックスのチェックを付けるか外すかを特定するための「目印」的な扱いです)

注目すべきコード②

次に見て頂きたいのは34行目です。

    For Each rng In ActiveWindow.RangeSelection

34行目のActiveWindow.RangeSelectionは、ここでは選択されたセルのことを指します。

このFor Eachステートメントで、選択されたセルの数だけループ処理を行います。

なお、このコードはワークシートのChangeイベント内の処理なので、セルの値が変更された後に選択されているセルがActiveWindow.RangeSelectionプロパティの対象になります。

(例えばC3のセルに値が入力されてEnterキーが推された場合はC4が対象のセル、他にはC3とC4のセルが選択された状態でDeleteキーが推された場合はC3とC4のセルが対象になります)

注目すべきコード③

次に見て頂きたいのは36行目から40行目、そして55行目です。

        If ActiveWindow.RangeSelection.Count = 1 Then
            
            '選択されているセルが1つの場合
        
            If Target.Row = rng.Row Then

                '値が変更されたセルの位置を取得する
                adrs = "C" & Target.Row

この36行目の条件は、選択されているセルの数が1つなのか2つ以上なのかを判定します。

そして40行目では「Target.Row」と「rng.Row」の値が一致しているかを判定します。

なぜこれらの条件判定を行うのかというと、値を変更した後にEnterキーを押したときにセルが1つ下に移動する(ようキーの操作が設定されている)場合に、値を変更したセルがActiveWindow.RangeSelectionに含まれないセルだからです。

※値を変更した後にEnterキーを押すと、ActiveWindow.RangeSelectionの対象は移動先のセルが対象になります。ですが、ここで欲しいのはあくまで変更されたセルです。

値が変更されてEnterキーを押すと、その変更されたセルの左隣りのチェックボックスの状態を変更したい場合に、左隣りではなくその一つ下のチェックボックスを変更してしまいます。

上から1つ目と2つ目のチェックボックスにチェックを「手動で意図的に」付けた状態。(マクロではセルが空白ならチェックはつけない)

また、C3からC7のセルまで値が入力されていない状態。

C3のセルに51以上100以下ではない値を入力した直後。

Enterキーを押した後にC4のセルが選択された状態。

C3のセルの隣のチェックボックスのチェックが外れて欲しいのに一つ下のチェックボックスのチェックが外れてしまっています。

なぜなら、Enterキーを押した後のセルの行位置を参照して、その行にあるチェックボックスのチェックを変更してしまうからです。

欲しいのはあくまで値が入力されたC3のセルの行(3)です。Enterキーを押した後のセルの行位置(4)は欲しくありません。

以上の回避策として、40行目で「Target.Row」と「rng.Row」の値が一緒かそうでないかを判定し、一致しない場合はC3のセルの隣のチェックボックスをチェックを付けるよう、55行目で「Target.Row」の値を行位置とします。(変数「adrs」に格納しておきます)

                '値が変更されたセルの位置を取得する
                adrs = "C" & Target.Row

Target.Rowには変更したセルの行位置が格納されています。

C3のセルを変更した後はTarget.Rowには「3」が格納されています。

Target.Rowによって正常に変更したセルの行位置が取得でき、C3のセルの左横にあるチェックボックスのチェックを変更することができるようになりました。

注目すべきコード④

次に見て頂きたいのは78行目から80行目です。

Sub cbx_checkONOFF(cbxNM As String, _
                   adrs As String, _
                   adrsAry() As Variant)

この78行目から、チェックボックスのチェックを付けるか付けないか(外すか)の設定を行うサブルーチン「cbx_checkONOFF」の本体の処理になります。

上記のコードでは、呼び出し元から受け取っているのは次の3つになります。

• cbxNM：チェックボックス名
• adrs：セルのアドレス位置
• adrsAry：セルのアドレス位置を格納する配列

注目すべきコード⑤

次に見て頂きたいのは92行目です。

    'セルの位置が配列に含まれているか判定
    If IsError(Application.Match(adrs, adrsAry, 0)) = False Then

変更されたセルがC3からC7のいずれであるか(判定はTrue)それ以外か(判定はFalse)を判定します。

C3からC7以外のセルは何もしないので、そのままcbx_checkONOFFから何もせずに抜けます。

注目すべきコード⑥

次に見て頂きたいのは95行目です。

        'チェックボックス名を取得する
        cbxNM = cbxNMAry(WorksheetFunction.Match(adrs, adrsAry, 0) - 1)

この95行目のWorksheetFunction.Match関数を使い、チェックを付けるか外すかその対象のチェックボックス名を取得します。

もしC3のセルが変更されたら、そのC3の左隣の「cbx_1」という名前のチェックボックスのチェックを、もしC4のセルが変更されたら、そのC4の左隣の「cbx_2」という名前のチェックボックスのチェックを・・・といった感じでチェックボックス名を取得します。

※セルの順番とチェックボックス名の順番を合わせています。

注目すべきコード⑦

次に見て頂きたいのは97行目から110行目です。

        If Sheets("top").Range(adrs).Value >= 51 And _
           Sheets("top").Range(adrs).Value <= 100 Then
    
            '値が51以上100以下の場合
    
            'チェックボックスのチェックを付ける
            Sheets("top").CheckBoxes(cbxNM).Value = xlOn
    
        Else
    
            'チェックボックスのチェックを外す
            Sheets("top").CheckBoxes(cbxNM).Value = xlOff
    
        End If

97、98行目でセルの値が51以上100以下の場合かを判定します。

51以上100以下の場合は103行目でチェックボックスにチェックを付け、51以上100以下ではない場合は108行目でチェックボックスのチェックを外します。

動作確認

セルの値を入力してマクロが実行された時のExcelの状態は次の通りです。

未入力のセルに51以上100以下の値が入力された場合

C3のセルに51の値を入力。

Enterキーを押すとC3のセルの左隣のチェックボックスのチェックが付きました。

未入力のセルに51以上100以下ではない値が入力された場合

C3のセルに50の値を入力。

Enterキーを押すとC3のセルの左隣のチェックボックスのチェックはついていないままの状態です。

セルに51以上100以下の値が入力されていて、51以上100以下の値以外の値が入力された場合

C3のセルに51の値が入力されています。

50の値を入力。

Enterキーを押すとC3のセルの左隣のチェックボックスのチェックが外れました。

セルに51以上100以下の値以外の値が入力されていて、51以上100以下の値が入力された場合

C3のセルに50の値が入力されています。

51の値を入力。

Enterキーを押すとC3のセルの左隣のチェックボックスのチェックが付きました。

最後に

本記事では、セル値の条件合致・相違に合わせてフォームコントロールのチェックボックスのチェック付与・解除を行う方法についてご説明しました。

チェックボックスのチェック付与・解除は、例えばチェックボックスに「cbx_1」という名前が付いている場合は「CheckBoxes(“cbx_1”).value」プロパティの値を設定することで行うことができます。

チェックボックスのチェックを付けるにはvalueプロパティに1を、OFFにしたい時は-4146の値を設定します。

なお、コードのxlOnとxlOffは定数で、xlOnは1、xlOffは-4146という値のことを意味します。

セル値の条件合致・相違に合わせてフォームコントロールのチェックボックスのチェック付与・解除を行いたい場合は参考にしてみてくださいね。

プログラミングのスキルを習得するなら

プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。

プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。

→　TechAcademyの「1週間 無料体験」はこちら