Excel5.0ダイアログExcel5.0ダイアログシートを用いたセル参照 2012.11.10 更新:2024.12.02
「Excel5.0ダイアログ」は、古いバージョンのExcelで、現在のユーザーフォーム代わりに使用されていたものです。
OptionButtonやTextBox(5.0ではEditBoxと呼ばれる)等が配置でき、マクロで制御します。
[ダイアログ例]
ユーザーフォームの方が拡張性が高く、イベントドリブン型のプログラムを使用できるため、私は基本的にユーザーフォームを使用していますが、ユーザーフォームからセル参照を取得する「RefEdit」コントロールは動作が不安定なため使用していません。
そのためプログラム上でユーザーにセルを参照してもらう必要がある処理では、「Excel5.0ダイアログ」使用しています。
このページは Excel5.0ダイアログで、セル参照するためのメモとサンプルコードです。
ポイント
・Excel5.0ダイアログシート
・選択中のセルアドレスをEditBoxに表示
・EditBoxが空白時の対応例
・セル参照の文字列からブック名・シート名、セルアドレスを取得
・セル参照範囲とデータで使用しているセル範囲の共通セルを取得
・参照先が複数の場合の対応
[サンプルファイル]ダウンロード
Excel5.0ダイアログシートを操作できるサンプルのExcelファイル
Excelマクロ管理ツール
Excel5.0ダイアログシートの挿入
1.シートタブを右クリック
2.挿入を選択→「挿入」ダイアログが表示される
3.「MS Excel5.0 ダイアログ」を選択し「OK」
EditBoxの挿入
1.「開発」タブを選択
2.挿入をクリック
3.「フォームコントロール」の「テキストフィールド」を選択
4.ダイアログシートの任意の場所をクリック→EditBoxが挿入される
EditBoxの属性選択
EditBoxを右クリックし「コントロールの書式設定」をクリックする。
「コントロールの書式設定」ダイアログが表示される。
ダイアログの「コントロール」タブを選択し、「入力できるデータ」を「参照先」に設定し[OK]ボタンをクリックする。
※「入力できるデータ」を「参照先」に設定しても、ダイアログシート上の EditBox の見た目に変化はない
コードから ダイアログ を呼び出し表示すると EditBox の端にセル参照用ボタンが追加されている
EditBoxの名前設定
EditBox選択すると名前ボックスに現在の名前が表示される。
名前のボックスでEditBoxに任意の名前を設定する
※ コードから EditBox を Index で取得する場合に オブジェクトを取得できないケースがあるらしいので EditBox に名前を設定することを推奨する
名前を設定した後は EditBox の選択状態を解除する(ダイアログの周りのグリッド表示箇所等をクリックする)。
(EditBoxが選択状態のままコードを実行すると動作が不安定になるケースがある)
Excel5.0ダイアログの表示
表示にはマクロを使用。以下はサンプルコード。
※"ダイアログ1"はシート名 古いバージョンのExcelではデフォルトのシート名は"Dialog1"である
Sub ダイアログシート表示() ' シートのShowメソッドでダイアログシートが表示される Dim blnShow As Boolean blnShow = ThisWorkbook.Sheets("ダイアログ1").Show ' "ダイアログ1"はシート名 ' ダイアログシートで[×]ボタンや[キャンセル]ボタンを ' クリックすると 戻り値に False が設定される If blnShow = False Then Exit Sub End Sub
コード実行結果
・ダイアログの[×]ボタンや[キャンセル]ボタンをクリックすると「False」が返る
・セル範囲とは関係のない文字列を入力し[OK]ボタンをクリックすると エラーが表示される
EditBox右端のボタンを押すと、下図のようにダイアログが折りたたむ。
EditBoxに選択中のセルアドレスを表示
ダイアログ表示時に、EditBoxに選択中のセルアドレスを表示するサンプルコード。
Sub EditBoxに選択中のセルアドレスを表示() '------------------------------ ' 選択セルのアドレスを取得 '------------------------------ Dim strSelectionAddress As String If TypeName(Selection) = "Range" Then strSelectionAddress = Selection.Address End If '------------------------------ ' EditBoxの設定 '------------------------------ ' EditBoxをオブジェクト変数に設定 ' ダイアログシート名:"ダイアログ1" ' EditBox名 :"sample" Dim objEditBox As Object Set objEditBox = ThisWorkbook.Sheets("ダイアログ1").EditBoxes("sample") ' EditBoxにセルアドレスを設定 objEditBox.Caption = strSelectionAddress '------------------------------ ' ダイアログシート表示 '------------------------------ Dim blnShow As Boolean blnShow = ThisWorkbook.Sheets("ダイアログ1").Show ' "ダイアログ1"はシート名 If blnShow = False Then Exit Sub '[×]ボタンや[キャンセル]ボタンを押下の場合は終了する '------------------------------ ' EditBoxのセル参照を表示 '------------------------------ MsgBox objEditBox.Caption End Sub
EditBox の文字列の取得・設定にはCaptionプロパティを使用する。
ダイアログシート上のEditBoxの名前を取得
ダイアログシートに存在する EditBox の名前を取得するサンプルコード。
Sub EditBoxの名前をイミディエイトウィンドウに表示() ' ダイアログシート名:"ダイアログ1" Dim obj As Object For Each obj In ThisWorkbook.Sheets("ダイアログ1").DrawingObjects If TypeName(obj) = "EditBox" Then Debug.Print obj.Name End If Next End Sub
EditBox を DrawingObjects として取得した場合、 TypeName関数で"EditBox"が戻り値となる。
EditBoxが空白で[OK]ボタンをクリック場合の対応
EditBoxが空白の状態で[OK]ボタンをクリックした場合に、再度ダイアログを表示するサンプルコード。
Sub EditBoxが空白の場合に再度ダイアログシートを表示() '------------------------------ ' EditBoxの設定 '------------------------------ ' EditBoxをオブジェクト変数に設定 ' ダイアログシート名:"ダイアログ1" ' EditBox名 :"sample" Dim objEditBox As Object Set objEditBox = ThisWorkbook.Sheets("ダイアログ1").EditBoxes("sample") '------------------------------ ' ダイアログシート表示 '------------------------------ Dim blnShow As Boolean Do blnShow = ThisWorkbook.Sheets("ダイアログ1").Show If blnShow = False Then Exit Sub '[×]ボタンや[キャンセル]ボタンを押下の場合は終了する ' EditBox の文字数が 1 以上の場合は抜ける Loop Until 0 < Len(objEditBox.Caption) '------------------------------ ' EditBoxのセル参照を表示 '------------------------------ MsgBox objEditBox.Caption End Sub
ダイアログを閉じた時に EditBox の値を調べ、空白の場合は Do ~ Loop で再度表示。
EditBoxの参照セルアドレスから該当セルのプロパティを取得・設定
EditBox の参照したセルアドレスの文字列から、該当セル範囲のセルの個数を取得し背景色を黄色にするサンプルコード。
Sub EditBoxの参照セルのプロパティを取得設定() '------------------------------ ' EditBoxの設定 '------------------------------ ' EditBoxをオブジェクト変数に設定 ' ダイアログシート名:"ダイアログ1" ' EditBox名 :"sample" Dim objEditBox As Object Set objEditBox = ThisWorkbook.Sheets("ダイアログ1").EditBoxes("sample") '------------------------------ ' ダイアログシート表示 '------------------------------ Dim blnShow As Boolean Do blnShow = ThisWorkbook.Sheets("ダイアログ1").Show If blnShow = False Then Exit Sub '[×]ボタンや[キャンセル]ボタンを押下の場合は終了する ' EditBox の文字数が 1 以上の場合は抜ける Loop Until 0 < Len(objEditBox.Caption) '------------------------------ ' 参照セルのプロパティ取得・設定 '------------------------------ ' 参照セルを設定 Dim rngTarget As Range Set rngTarget = Application.Range(objEditBox.Caption) ' 参照セル個数取得 MsgBox rngTarget.Cells.CountLarge ' 参照セルの背景色を黄色に設定 rngTarget.Interior.Color = vbYellow End Sub
他ブック・他シートのセル参照も可能。
EditBoxの参照セルアドレスからブック名・シート名・セルアドレスを取得
EditBox のセル参照文字列から、ブック名、シート名、セルアドレスを取得するサンプルコード。
Sub EditBoxの参照セルからブック名シート名セルアドレス取得() '------------------------------ ' EditBoxの設定 '------------------------------ ' EditBoxをオブジェクト変数に設定 ' ダイアログシート名:"ダイアログ1" ' EditBox名 :"sample" Dim objEditBox As Object Set objEditBox = ThisWorkbook.Sheets("ダイアログ1").EditBoxes("sample") '------------------------------ ' ダイアログシート表示 '------------------------------ Dim blnShow As Boolean Do blnShow = ThisWorkbook.Sheets("ダイアログ1").Show If blnShow = False Then Exit Sub '[×]ボタンや[キャンセル]ボタンを押下の場合は終了する ' EditBox の文字数が 1 以上の場合は抜ける Loop Until 0 < Len(objEditBox.Caption) '------------------------------ ' 参照セル設定 '------------------------------ Dim rngTarget As Range Set rngTarget = Application.Range(objEditBox.Caption) '------------------------------ ' 各プロパティ取得 '------------------------------ ' ブック名取得 Dim strBookName As String strBookName = rngTarget.Worksheet.Parent.Name ' シート名取得 Dim strSheetName As String strSheetName = rngTarget.Worksheet.Name ' セルアドレス取得 Dim strAddress As String strAddress = rngTarget.Address(False, False) '------------------------------ ' 結果表示 '------------------------------ MsgBox "ブック名:" & strBookName & vbLf & _ "シート名:" & strSheetName & vbLf & _ "セルアドレス:" & strAddress End Sub
セルの参照例
●ブック名
他ブックを参照した場合は、ブック名の前後に [ ] が付く。
●シート名
他シートを参照した場合、シート名の後に「!」が付く。
●セルアドレス
A1形式で、行列ともに絶対参照の「$」が付く。
複数範囲を選択の場合は「,」で区切られる。
●その他
ブック名、シート名に「'」(シングルクオンテーション)が入っている場合、
ブック名、「!」、「'」、それぞれの前に「'」が追加される。
例)ブック名:Book1 シート名:Sheet'2 セルアドレス:$A$1:$B$3
処理効率化のポイント
セル参照範囲を取得するケースでは、データの取得が目的であることが多い。
データを取得する場合には「セル参照範囲」と「データで使用しているセル範囲」の共通セルを取得することで、処理の効率化を図れる。
Sub EditBoxのセル参照とシートの使用セル範囲の共通セルを取得() '------------------------------ ' EditBoxの設定 '------------------------------ ' EditBoxをオブジェクト変数に設定 ' ダイアログシート名:"ダイアログ1" ' EditBox名 :"sample" Dim objEditBox As Object Set objEditBox = ThisWorkbook.Sheets("ダイアログ1").EditBoxes("sample") '------------------------------ ' ダイアログシート表示 '------------------------------ Dim blnShow As Boolean Do blnShow = ThisWorkbook.Sheets("ダイアログ1").Show If blnShow = False Then Exit Sub '[×]ボタンや[キャンセル]ボタンを押下の場合は終了する ' EditBox の文字数が 1 以上の場合は抜ける Loop Until 0 < Len(objEditBox.Caption) '------------------------------ ' 参照セル設定 '------------------------------ Dim rngTarget As Range Set rngTarget = Application.Range(objEditBox.Caption) '------------------------------ ' 共通セルを取得 '------------------------------ Dim rngIntersect As Range Set rngIntersect = Intersect(rngTarget, rngTarget.Worksheet.UsedRange) '------------------------------ ' 結果表示 '------------------------------ Dim strMessage As String strMessage = "EditBox参照セル:" & rngTarget.Address(False, False) & vbLf & _ "シート使用セル:" & rngTarget.Worksheet.UsedRange.Address(False, False) & vbLf If rngIntersect Is Nothing Then strMessage = strMessage & "共通セル:なし" Else strMessage = strMessage & "共通セル:" & rngIntersect.Address(False, False) End If MsgBox strMessage End Sub
参照元のセル範囲が複数の場合の対応
EditBox でセルを参照する際に、[Ctrl]キーを押しながらセルを選択すると、セル範囲が「,」で区切られ複数のセル範囲を選択できる。
参照元のセル範囲が複数の場合に、
参照元のセル範囲毎にプロパティを取得・設定したい場合には、Range オブジェクトの Areas プロパティを利用すると良い。
以下、サンプルコード。
Sub EditBoxのセル参照が複数ある場合の対応処理例() '------------------------------ ' EditBoxの設定 '------------------------------ ' EditBoxをオブジェクト変数に設定 ' ダイアログシート名:"ダイアログ1" ' EditBox名 :"sample" Dim objEditBox As Object Set objEditBox = ThisWorkbook.Sheets("ダイアログ1").EditBoxes("sample") '------------------------------ ' ダイアログシート表示 '------------------------------ Dim blnShow As Boolean Do blnShow = ThisWorkbook.Sheets("ダイアログ1").Show If blnShow = False Then Exit Sub '[×]ボタンや[キャンセル]ボタンを押下の場合は終了する ' EditBox の文字数が 1 以上の場合は抜ける Loop Until 0 < Len(objEditBox.Caption) '------------------------------ ' 参照セル設定 '------------------------------ Dim rngTarget As Range Set rngTarget = Application.Range(objEditBox.Caption) '------------------------------ ' 結果表示 '------------------------------ Dim i As Long Dim strAddress As String For i = 1 To rngTarget.Areas.Count strAddress = strAddress & "セル参照" & i & ":" & rngTarget.Areas(i).Address(False, False) & vbLf Next MsgBox strAddress End Sub
VBAコードをカラーで印刷・Web掲載するためのツールはこちら
その他メモ
ダイアログシートの表示・非表示切り替え
ダイアログシートはワークシートと同様にシートを表示・非表示を切り替えられる。
ユーザーに配付する場合等は、ダイアログシートは非表示にしておくことが望ましい。