トップ　>　備忘録　>　Excel5.0ダイアログでのセル参照

Excel5.0ダイアログExcel5.0ダイアログシートを用いたセル参照　　　2012.11.10　　　更新：2024.12.02

「Excel5.0ダイアログ」は、古いバージョンのExcelで、現在のユーザーフォーム代わりに使用されていたものです。
OptionButtonやTextBox（5.0ではEditBoxと呼ばれる）等が配置でき、マクロで制御します。
［ダイアログ例］


ユーザーフォームの方が拡張性が高く、イベントドリブン型のプログラムを使用できるため、私は基本的にユーザーフォームを使用していますが、ユーザーフォームからセル参照を取得する「RefEdit」コントロールは動作が不安定なため使用していません。

そのためプログラム上でユーザーにセルを参照してもらう必要がある処理では、「Excel5.0ダイアログ」使用しています。
このページは Excel5.0ダイアログで、セル参照するためのメモとサンプルコードです。

2021.04.12　このページ内容は、Microsoft365ではエラーになるとの記事があったため加筆・修正しました。

ポイント

・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掲載するためのツールはこちら



その他メモ

ダイアログシートの表示・非表示切り替え

ダイアログシートはワークシートと同様にシートを表示・非表示を切り替えられる。
ユーザーに配付する場合等は、ダイアログシートは非表示にしておくことが望ましい。


ページトップへ戻る
Copyright(C) 2009- 坂江 保 All Rights Reserved.