セルを参照するボタン

  1. Home
  2. Excel
  3. VBA
  4. Excel VBA Tips

上図のように、処理の対象とするセル範囲をユーザーに選択してもらいたい場合があります。表示しているコントロールは、Excelの標準機能でよく使われていますので、ビギナーが操作するときでも理解しやすいでしょう。ちなみに、テキストボックスの右にある、クリックするとダイアログボックスが小さくなるボタンの正式名称は[ダイアログ最小化]ボタンです。

この[ダイアログ最小化]ボタンを持つコントロールの正体はRefEditコントロールです。UserFormで使うには次のようにします。

  1. VBEで[ツールボックス]ツールバーの余白を右クリックする
  2. 表示されるショートカットメニューで[その他のコントロール]をクリックする
  3. [コントロールの追加]ダイアログボックスで「RefEdit.Ctrl」をオンにする
  4. [OK]ボタンをクリックする

こうして[ツールボックス]ツールバーに追加したRefEditは、その他のコントロールと同じようにUserForm上で使用できます。ただし、実行時に表示されるのは次のようなコントロールです。

[ダイアログ最小化]ボタンにアイコンが表示されていませんね。なんとなく違和感を感じます。上記の方法で追加したRefEditコントロールは、Excelとは関係ない外部のコントロールですから、Excelで使われているアイコンが表示されなくてもしかたありません。また、外部のコントロールということは、万が一RefEditコントロールがインストールされていないパソコンでは、このUserFormを使うことはできないという不安もあります。そうしたデメリットを避けたいのでしたら、Excel自身が持つ標準の[ダイアログ最小化]ボタンを使いましょう。ちょっと手間がかかりますけど、見た目は完璧です。

実は、冒頭でお見せしたダイアログボックスはUserFormではありません。Excel 5.0/95時代に使われていたDialogシートなのです。Dialogシートについての詳細な解説は割愛しますが、Dialogシートで使える機能はすべてExcel自身が持つ標準機能です。ほら、よく見るとコマンドボタンの形も微妙に違うでしょ。少し角が丸くなっています。Excelのさまざまな機能で表示されるコマンドボタンと同じです。ここでは、このDialogシートを使って[ダイアログ最小化]ボタンを使う方法を解説します。

まず、Excelの画面でシート見出しを右クリックして[挿入]を選択します。実行すると、さまざまなシートを挿入できる[挿入]ダイアログボックスが表示されます。

[標準]タブで「MS Excel 5.0ダイアログ」を選択して[OK]ボタンをクリックします。実行すると、新しいDialogシート[Dialog1]が挿入されます。このシート名は後で使いますから、覚えておいてください。

設計のしかたはUserFormと似ています。ツールバーから[ラベル]と[エディットボックス]を配置し、初めから用意されている[OK]ボタンと[キャンセル]ボタンの位置を変更します。ついでに、ダイアログボックスのキャプションも書き換えましょう。

次に、配置したエディットボックスを右クリックして[コントロールの書式設定]を実行します。表示される[コントロールの書式設定]ダイアログボックスの[コントロール]タブで、「入力できるデータ」グループの[参照先]をオンにして[OK]ボタンをクリックします。

これでDialogシートの設計は完了です。あとは使い慣れたVBAから、Dialogシートを呼び出すだけです。VBEを起動して、標準モジュールに次のマクロを記述してください。ここでは、指定したセルの背景色を赤色にしています。

Sub Sample1()
    With Sheets("Dialog1")          ''(1)
        .EditBoxes(1).Text = ""     ''(2)
        If .Show = True And .EditBoxes(1).Text <> "" Then       ''(3)
            Range(.EditBoxes(1).Text).Interior.ColorIndex = 3   ''(4)
        End If
    End With
End Sub

Dialogシートの使い方は、今のUserFormと少し異なりますので、詳しく解説しましょう。

(1)Dialogシートはワークシートと同じシートの仲間ですから、Sheets("Dialog1")と呼ばなければなりません。ダイアログを表示するだけなら、Sheets("Dialog1").Showと書きます。

(2)Dialogシートはシートですから、UserFormのような"初期化(Initialize)"イベントは発生しません。ただアクティブシートを切り替えるのと同じです。したがって、エディットボックスに格納されたテキストや、オプションボタン、チェックボックスの状態、リストボックスやコンボボックスに登録したデータなどは、前回の結果がそのまま保持されます。ワークシート上のセルに値を代入したのと同じ感覚です。そこで、ダイアログボックスを表示する前に、前回指定されたアドレスをクリアしています。前回の選択範囲をあえて表示したいのでしたら、この行は不要です。

(3)ここが、UserFormと最も異なる部分です。この行では2つのポイントがあります。

まずShowメソッド。Dialogシートを表示するにはShowメソッドを実行します。このShowメソッドは、どのボタンがクリックされたという返り値を返します。Dialogシートを挿入したとき、初めから用意されていた[OK]ボタンは、実は「標準ボタン」がオンになっていたのです。疑う方は[OK]ボタンを右クリックして確認してください。Dialogシートを表示するShowメソッドは、「標準ボタン」がクリックされるとTrueを返し、「キャンセルボタン」がクリックされるとFalseを返します。これでユーザーの操作を判定します。Excel 95までは、こんな苦労をしていたんですね～懐かしいです…。

さて、もうひとつのポイントはコントロールの指定方法です。現在のVBAでは、コントロールを直接コントロール名で指定できます。TextBox1.Textとか、住所.txtとか。しかし、Excel 5.0/95マクロではコレクションとして指定しなければなりません。Worksheets("Sheet1")のような感覚です。エディットボックスのコレクションはEditBoxesですから、EditBoxes(1).Textとします。万が一、何もセル範囲を指定しないまま[OK]ボタンが押された場合を想定して、念のためにチェックしています。

(4)ここは、いつものVBAと同じです。エディットボックスに代入されたセルのアドレスをRangeオブジェクトに渡して、InteriorオブジェクトのColorIndexに3を設定しています。

使い慣れないDialogシートですから、VBAとは違和感があるかもしれませんね。まぁ、どうしても標準の[ダイアログ最小化]ボタンを使いたい場合には、検討してください。