作者別: it-yobi

マクロの実行ボタンが配置されたExcelファイルを使っている際に、「実行ボタンをうっかり押してしまった…」という経験、ありませんか？
特に重要な処理を含むマクロでは、誤操作が思わぬトラブルにつながることもあります。

そんな時におすすめなのが、「確認画面」を実行時に表示する方法です。
実行ボタンを押した際に、ユーザーに実行してよいのか確認を求めることで、誤操作を防ぐことができます。

※こちらで実現したファイルは、記事の最後にて配布しています。

1. 設定

まずは、「確認画面」を表示したい対象の実行ボタンの上で右クリックし、[マクロの登録]を選択します。

[マクロの登録]画面が表示されましたら、[編集]を選択します。

[編集]を選択すると、対象のプロシージャ（Sub～End Sub）が表示されます。
こちらでは、以下の処理が表示されたとします。

Sub UpdateData()
    
    Range("C4:C23").Value = Range("D4:D23").Value
    Range("D4:D23").ClearContents
    
End Sub

後は、このプロシージャ内に「確認画面」を表示する処理を記述します。
具体的には、元々の処理の先頭に、以下のコードを記述します。

Sub UpdateData()
    
    If MsgBox("本当に実行しますか？", _
        vbExclamation + _
        vbYesNo + vbDefaultButton2) = vbNo Then Exit Sub
    
    Range("C4:C23").Value = Range("D4:D23").Value
    Range("D4:D23").ClearContents
    
End Sub

「If」の処理は、「Then」の後に１つの処理を記述することで、「End If」を省略することができます。
複数の処理を記述する場合は、「Then」の後で改行して、処理を「End If」で囲む必要があります。
「□_」（□は半角スペース）は、本来改行しない位置で改行する場合に入力します。
こちらでは、コードの全体が見やすいように改行しています。

メッセージボックスを表示する処理を追記しています。
それぞれの設定値は、以下のような意味です。

以下のコードにて、表示されたメッセージボックスで「はい」を選択した場合は「vbYes」、「いいえ」を選択した場合は「vbNo」の値が返されます。

MsgBox("本当に実行しますか？", vbExclamation + vbYesNo + vbDefaultButton2)

そのため、その結果が「vbNo」の場合に、処理を終了する旨を記述しています。

If MsgBox(…) = vbNo Then Exit Sub

2. 完成

以上の手順で完成です。
実行ボタンを押すと、警告音付きの確認画面が表示されます。
また、「いいえ」がデフォルト選択なので、Enterキーを誤って押しても実行されません。
「はい」を選択したときのみ、マクロが実行されます。

3. まとめ

既存のコードの先頭に追記するだけで、マクロの誤操作を防止できます。
そのため、誤操作に悩まされている場合は、ぜひ取り入れてほしいテクニックです。

▼サンプルファイル▼

以下は、従業員を管理するための表です。

「勤務形態」によって、ある程度決まった入力パターンが存在します。
毎回同じような内容を手入力するのは、正直かなり手間です。
とはいえ、勤務形態ごとに例外が発生することもあるため、VLOOKUP関数などの数式だけでは柔軟に対応できません。

そこで今回は、「勤務形態」に応じたテンプレートデータを自動で入力する仕組みを実現する方法について解説していきます。

※こちらで実現したファイルは記事の最後にて配布しています。

1. 開発準備

今回は、上のテンプレート一覧の表の各行に入力ボタンを設けます。
そして、ボタンを押すと同時に、下の表の最終行に値のみが入力されるといった仕組みを開発します。

ただ、テンプレートの数が多い場合などは、ボタンを複数配置するのは大変です。
そのため今回は、実際のボタンではなく、ボタン風にデザインしたセルの上を選択すると同時に、処理が実行されるといった仕組みにしていきます。

ボタン風のデザインは、罫線や背景色などの書式設定を活用して実現しています。

今回のように、「特定のシートの特定のセルを選択すると同時に、何かしら処理を実行する」、そのような仕組みを実現するには、「シートモジュール」を活用します。

シートモジュールは、該当するシートのタブ上で右クリックし、[コードの表示]を選択することで表示することができます。

選択すると、以下のエディタ画面（VBE）が表示されます。
また、該当するシートのシートモジュールが表示された状態になります。
「Option Explicit」は、VBEの設定内容次第では表示されません。「Option Explicit」についての解説はこちらでは省略します。

次に、該当するシートのセルを選択すると同時に処理が実行される特殊なプロシージャ（イベントプロシージャ）を用意する必要があります。
そのためには、シートモジュールの左上のリストから「Worksheet」を選択します。

「Worksheet」を選択すると、自動で「Worksheet_SelectionChange」というプロシージャが表示されます。
このプロシージャが、該当するシート上のいずれかのセルが選択されると同時に処理が実行されるイベントプロシージャになります。

2. コードの記述

以下のコードを記述します。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    If Target.CountLarge= 1 Then
        If Target.Row >= 3 And _
            Target.Row <= 5 And _
            Target.Column = 1 Then
            
            Dim tR As Long, lR As Long
            tR = Target.Row
            lR = Cells(Rows.Count, "A").End(xlUp).Row + 1
            
            Range(Cells(lR, "B"), Cells(lR, "D")).Value = _
                Range(Cells(tR, "B"), Cells(tR, "D")).Value
            
        End If
    End If
    
End Sub

では、コードについて解説していきます。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    If Target.CountLarge= 1 Then
        '省略
    End If
    
End Sub

「Worksheet_SelectionChange」というプロシージャの引数の「Target」に、選択されたセルの情報が渡されるため、そのセルの情報から、表の対象のセルが選択されたかどうかを確認しています。
こちらでは、選択されたセルが１つのみであることを確認しています。

        If Target.Row >= 3 And _
            Target.Row <= 5 And _
            Target.Column = 1 Then
            
            '省略
            
        End If

次に、選択されたセルの行番号が３以上、５以下、列番号が１であることを確認しています。
これは、ボタン風のデザインがされたセルの範囲内を指しています。

            Dim tR As Long, lR As Long
            tR = Target.Row
            lR = Cells(Rows.Count, "A").End(xlUp).Row + 1

以上の条件を満たしたときに、変数（tR）に選択されたセルの行番号、変数（lR）に下の表の最終行に１を加えた数値を格納しています。
変数（lR）に関しては、「Cells(Rows.Count, “A”)」でシートの末尾のA列のセルを指定し、「.End(xlUp)」で「Ctrl+↑」と同じように移動し、その止まった位置の行番号（.Row）に１を加えています。

            Range(Cells(lR, "B"), Cells(lR, "D")).Value = _
                Range(Cells(tR, "B"), Cells(tR, "D")).Value

最後に、先ほど取得した変数（tR、lR）を活用し、下の表の、最終行の１つ下の行のＢ列からＤ列に、選択された行のテンプレート一覧の表のB列からD列の値を入力しています。

3. 完成

以上の内容で完成です。
テンプレート一覧表の隣の「入力」と書かれたセルを選択すると同時に、下の表の末尾に自動で反映されます。

また、数式ではなく直接値が入力されるため、必要に応じて直接修正することもできます。

テンプレート一覧表を更新したとしても、既存の登録データには影響ありません。

▼サンプルファイル▼

「表の中の特定のデータのみを残したい」

そのような場面、ありますよね。
特に、特定のデータのみを他の方に共有するときなどです。

ただ、標準機能には、「特定のデータ以外を一括で削除する」という機能はありません。
そのため、「特定のデータ以外で絞って、残ったデータ行を削除してから絞り込みを解除する」といった手順を行う必要があります。

例えば、以下の表から「地域が東京のデータのみ」を残したい場合、以下の手順になります。

---

① 東京以外で絞り込み

② 表示されたデータ行を削除

③ 絞り込みを解除（フィルターの解除）

---

ただ、「削除したいデータで絞り込みする」というのが直観的じゃないです。

そこで今回は、「残したいデータ」で絞り込んでから非表示のデータ行を一括削除できるように、非表示の行を一括削除する機能の開発方法について解説していきます。

※こちらで開発したファイルは、記事の最後にて配布しています。

1. 開発準備

まず初めに、[開発]タブを選択し、[マクロ]を選択します。

表示された以下の画面にて、開発する機能の名前を入力し、[作成]を選択します。
こちらでは、「DeleteHiddenRows」と入力しています。

次に表示される以下の画面の「Sub ○○」から「End Sub」の間にコードを記述します。

2. コードの記述

以下のコードを記述します。

Sub DeleteHiddenRows()
    
    Dim r As Long
    For r = 100000 To 3 Step -1
        If Rows(r).Hidden Then
            Rows(r).Delete
        End If
    Next r
    
End Sub

では、コードについて解説していきます。

    Dim r As Long
    For r = 100000 To 3 Step -1
        '省略
    Next r

100000から3までを-1ずつ変数「r」に渡して、ForからNextの間を繰り返しています。
100000から3というのは、表の最終行（仮）から先頭行までを表しており、最終行の100000は、「100000行目まではデータがない」という前提の仮の値になります。

        If Rows(r).Hidden Then
            Rows(r).Delete
        End If

繰り返し処理で受け取る変数「r」の値を活用して、「r」行目が非表示の場合に「r」行目を行ごと削除しています。

3. ショートカットの割り当て

開発した機能が簡単に実行できるように、ショートカットを割り当てていきます。
まず、[開発]タブを選択し、[マクロ]を選択します。

マクロの画面から先ほどの開発したマクロ名を選択し、[オプション]を選択します。

表示された以下の画面にて、[ショートカットキー]のテキストボックスを選択し、好みのキーを入力します。
※「Ctrl+Shift+○」のようにShiftを加えたい場合は、「Shift+好みのキー」を入力します。

こちらでは、「Ctrl+Shift+D」に割り当てました。
ショートカットキーの入力後は、確定して画面を閉じます。

4. 完成

以上の手順で完成です。
抽出したいデータのみに絞り込みし、割り当てたショートカットキーを入力することで、非表示の行がすべて削除されます。

必要に応じて、フィルター機能を解除していただいて問題ございません。
また、今回の機能は、フィルター機能による絞り込みでなくても、非表示の行があれば、削除対象となります。

▼サンプルファイル▼

新機能「セルにフォーカス」は、選択中のセルの行と列をハイライト表示してくれる便利な機能です。

しかし、現時点では最新のバージョンでしか、この機能は使えません。
そこで、こちらの記事では、「セルにフォーカス」に似たような機能を自作する方法について解説していきます。

※こちらで作成したファイルは、記事の最後にて配布しています。

1. 条件付き書式の活用

「セルにフォーカス」という機能は、選択しているセルと同じ行、列のセルの背景を色付けするというものです。
そのような特定の条件を満たしたときのみにセルの色を変更する場合は、「条件付き書式」を活用します。

---

１．色付けしたい対象の範囲を選択

２．[ホーム]タブから[条件付き書式]の[新しいルール]を選択

３．表示された画面のルールの種類を[数式を使用して、書式設定するセルを決定]にし、以下の数式を入力

=XOR(ROW(A1)=CELL("row"),COLUMN(A1)=CELL("col"))
// セルA1は選択基準のセル（アクティブセル）

数式の意味は以下になります。

XOR：いずれかの条件を１つのみ満たしている場合
ROW(A1)=CELL("row")：対象のセルの行番号がアクティブセルの行番号と同じ
COLUMN(A1)=CELL("col")：対象のセルの列番号がアクティブセルの列番号と同じ

４．[書式]を選択し、[塗りつぶし]タブから好みの色を選択

５．確定

---

これで、アクティブセルと同じ行、列のセルの背景色を色付けすることができます。

ただし、他のセルを選択しても、色が更新されません。
色を更新するためには、セルを編集したり、F9キーを押したりして、数式を再計算する必要があります。

毎回、数式を再計算するのは大変ですので、次の設定を行います。

2. 数式を自動で再計算させる

セルを移動した時に、自動で数式を再計算して、セルの色が更新されるようにしていきます。

特定のシートのセルを選択すると同時に、何かしらの処理を実行するには、該当するシートのシートモジュールを活用します。

シートモジュールは、該当するシートのタブ上で右クリックし、[コードの表示]を選択することで表示することができます。

選択すると、以下のエディタ画面（VBE）が表示されます。
また、該当するシートのシートモジュールが表示された状態になります。
「Option Explicit」は、VBEの設定内容次第では表示されません。「Option Explicit」についての解説はこちらでは省略します。

次に、該当するシートのセルを選択すると同時に処理が実行される特殊なプロシージャ（イベントプロシージャ）を用意する必要があります。
そのためには、シートモジュールの左上のリストから「Worksheet」を選択します。

「Worksheet」を選択すると、自動で「Worksheet_SelectionChange」というプロシージャが表示されます。
このプロシージャが、該当するシート上のいずれかのセルが選択されると同時に処理が実行されるイベントプロシージャになります。

このプロシージャの中に、「数式を再計算する」という以下の処理を記述します。

Application.Calculate

3. 完成

以上の手順で完成です。
セルを選択すると同時に、数式が再計算され、選択しているセルがフォーカスされるようになります。

▼サンプルファイル▼

4. 注意点

自作する場合は、以下の点を考慮する必要があります。

・マクロ有効ブック（.xlsm）として保存する必要がある（マクロを使用しているため）
・数式が多いシートでは処理が重たくなる可能性がある（セルを選択する度、数式が再計算されるため）
・セルのコピー操作が解除されてしまうことがある（再計算と同時にコピーモードがキャンセルされるため）