えくせるちゅんちゅん

ことりがエクセルをちゅんちゅんするブログ

MENU

Excelの入力規則のリスト選択を簡単にするアドインを作ってみた

今日はExcelの入力規則のリスト選択の使いづらさを改善するアドインを作ってみたので紹介します。

f:id:Kotori-ChunChun:20200412171409p:plain


今回作ったもの

読者の皆さんはExcelで入力規則のリストって使ってますか?

これ、使いにくくないですか?


今回作成したアドインを使うことで、既存のブックに一切の手を加えることなくリスト選択が楽になります!


このアドインの仕様

以前作成した セル結合禁止アドイン や 作業グループ禁止アドイン と同様に「Excelアドイン」です。

ダウンロード

プログラムはxlam形式のアドインファイルに含まれているので、どんなブックであろうと共通のマクロを動作させることが出来ます。

アドインの実行は必要な時だけダブルクリックで立ち上げても良いですし、アドインフォルダにインストールして常駐するようにしても大丈夫です。

※ファイルのプロパティより、「許可する」にチェックを入れないと開けない場合があるようです。

f:id:Kotori-ChunChun:20200411035325p:plain

ソースコードは自由に改変していただいても構いませんが、使用は全て自己責任でお願いします。

再配布も自由ですが、常識的な範囲内でお願いします。

ダウンロード


使用風景

アドインを起動するとリボンに下図のようなタブが増えます。

「監視開始」をクリックすると、Excel全体に対してダブルクリックイベントを監視します。

アドイン起動とともに自動的に監視を開始するので、通常は押す必要がありません。

「監視停止」かマクロを中断したときのみ、改めて「監視開始」を押す必要があります。


ダブルクリックイベントを検知した時、該当セルに入力規則のリストが登録されている場合のみ選択フォームが表示されるようになります。


機能一覧

  • ダブルクリックで起動
    • 入力規則リストの入ったセルをダブルクリックしたときのみ、このフォームが起動します。
    • 少しマクロをイジれば右クリック起動や、セル選択時起動も簡単にできます。
    • ボタンを押したらフォームが起動というプログラム変更も容易です。
  • ダブルクリックで決定
    • 操作の手間を減らすため、ダブルクリックで決定です。OKボタンは設けてません。
    • 一応、Enterキーでも確定できます。
  • ESCでキャンセル
    • ESCを押した瞬間に窓が閉じます。
    • ×を押した場合もキャンセルです。
  • インクリメンタルサーチに対応
    • 上の入力ボックスに文字を入力すると、リアルタイムに選択肢が減っていきます。
  • 部分一致・完全一致検索に対応
    • 比較方法は 値 Like "*" & 検索文字列 & "*" なので、文法違反すると ERROR になります。
    • 正規表現、前方、後方、AND、ORへの対応はしてません。
  • 択一選択、複数選択に対応
    • ただし複数選択はプログラムを一言だけ修正が必要です。
    • 気が向いたら設定画面で選べるようにする予定。
  • 入力済みの値を選択した状態で表示
    • 簡単に作ると直前に入っていた値を考慮した表示が出来ませんが、このマクロは入力済みの値を検索して選択状態にした上で表示します。
    • 入力済みデータをカンマ区切りで分解して複数選択状態の復元も対応しています。


既知の不具合とか

  • カンマが含まれたデータには対応していません。
  • モードレスフォームには対応してません。
  • 設定画面は枠だけ用意してありますが未完成です。作って公開するかも未定です。
  • VBAのリストボックスは、マウスのホイールスクロールができません。なんとかしたいです。 アイテムをクリックしたまま上下に動かすと勝手にスクロールするので、ちょっと早すぎて使いづらいですがそれでしのいでください。

追記

リストボックスのスクロールに対応するコードが見つかりました。

一応動きましたがAPIを駆使しているので、よく動作確認をする必要がありそうです。

配布物には導入しませんので、必要な人は挑戦してみてください。

https://social.msdn.microsoft.com/Forums/en-US/7d584120-a929-4e7c-9ec2-9998ac639bea/mouse-scroll-in-userform-listbox-in-excel-2010?forum=isvvba


プログラム

ソースコード全文は GitHub を参照願います。

github.com

要となる ValidationListSelector.clsだけ説明しておきます。


いつものアドインと同じように、WithEventsExcel.Applicationを監視します。

今回はダブルクリックなので、app_SheetBeforeDoubleClickとしました。

Public WithEvents app As Excel.Application

Private Sub app_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Call OpenValidationList(Sh, Target, Cancel)
End Sub


イベントプロシージャの中に本文を書くと、後々の拡張に支障が出るので切り出しました。

これで、右クリックに対応したいときは、app_SheetBeforeRightClickからこれを呼ぶだけです。

Sub OpenValidationList(ByVal Sh As Object, ByVal Target As Range, ByRef Cancel As Boolean)End Sub


該当のセルが目的のセルか判定するためにガード節を設けます。

最初は↓を書いたのですが・・・

Dim rngVali As Range
Application.EnableEvents = False
On Error Resume Next
Set rngVali = Target.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
Application.EnableEvents = True
If rngVali Is Nothing Then Exit Sub

If Intersect(Target, rngVali) Is Nothing Then Exit Sub

If Target.Validation.Type <> XlDVType.xlValidateList Then Exit Sub

Dim Formula: Formula = Target.Validation.Formula1
If Formula = "" Then Exit Sub

On Errorが必要になってしまったので、丸投げにすることにしました。

On Error Resume Next
If Target.Validation.Type <> XlDVType.xlValidateList Then Exit Sub

Dim Formula: Formula = Target.Validation.Formula1
If Formula = "" Then Exit Sub
On Error GoTo 0


入力規則のリストの数式には、外部を参照する数式と、コンマ区切り定数の2パターンがあるため、頭文字がイコールかどうかで判定しました。

数式の場合は、Evaluateで計算すれば1列の二次元配列が返るのでTransposeして一次元配列化して取得です。

'入力リストの選択項目を取得
Dim list As Variant
If Formula Like "=*" Then
    list = Application.Evaluate(Formula)
    list = WorksheetFunction.Transpose(list)
Else
    list = Split(Formula, ",")
End If


フォームの実行は、標準メソッドのShowは使用しません。

コンストラクタが無く、起動時に引数を与える事ができないからです。

代わりにOpenFormメソッドを実装しました。

(宣言部)

Rem 引数を指定してフォームを表示する
Rem
Rem @param arr_listitems        リストに表示する全アイテム配列
Rem @param arr_defaultvalues    起動時に選択状態にするアイテム配列
Rem @parem can_multiselect      複数選択の可否
Rem
Rem @return As Variant(0 to n)  選択されていたアイテム配列
Rem                             キャンセル時:Null
Public Function OpenForm(arr_listitems, arr_defaultvalues, can_multiselect As Boolean) As Variant
    ・
    ・
    Me.Show '←モーダルフォームではここでVBAが止まる
    ・
    OpenForm = Me.Result'←戻り値の設定
    ・
End Function


こんな感じで使います。

(呼び出し部)

'初期状態で選択する値
Dim def As Variant
def = Split(Target.Value, ",")

'フォームの実行と結果の取得
Dim Result
Result = ListSelectorForm.OpenForm(list, def, False)

'キャンセルされたのでなければ、結果をコンマ区切りで連結してセルに入力
If Not IsNull(Result) Then
    Target.Value = Join(Result, ",")
End If

MsgBoxと同じような感じで使えるので、コードがスッキリして綺麗ですよね?(我ながら綺麗に分離できました)


インクリメンタルサーチの部分は省略します。


ハマったこととか

ユーザーフォームのデザイナを開いたままデバッグするとVBAがリセットされる

デバッグしている最中に、ユーザーフォームのデザイナを開くとVBAがリセットされるのは、使ったことのある方なら誰でもご存知かと思います。

この仕様が災いしてか、デザイナを開いたままマクロを実行して(自動的にデザイナが非表示になり)DoubleClickイベントなどを起こすと、マクロが完結してExcelに処理が戻ると同時にデザイナが再表示されVBAのリセットがかかります。

このため、今回のようなアドイン開発ではイベント監視のためのクラスインスタンスが初期化されて何度か苦労しました。

しかも、時々オートメーションエラーが出てExcelもクラッシュしました。

↓詳細


SpecialCellsメソッドはSelectionChangeイベントを発生させる

入力規則の判定部分でバッサリ消したところで、SpecialCellsメソッドを使用していたのですが、SelectionChangeイベントが発生することが判明し、何度かExcelがクラッシュしました。

機能からは予測しづらいのですが、そういうものなようです。

対処法は上記のようにApplication.EnableEvents = Falseとするしかなさそうです。


↓詳細


まとめ

このアドインを使えば、誰にも迷惑をかけずに自分だけが快適にリスト選択ができるようになります。

これでもう、あの使いづらいリストとはオサラバです!!!

快適なExcel生活を送りましょう!

以上


何か御座いましたらコメント欄、またはTwitterからどうぞ♪

それではまた来週♪ ちゅんちゅん(・8・)

プライバシーポリシー