連動するリストボックスの作り方について

Question

先日こちらで教えていただいたのですが、
途中でわからなくなってしまったので教えてください。
https://teratail.com/questions/257404

ユーザーフォームでリストボックスを二つ作って、
一つ目のリストには同じフォルダにあるファイル名が表示されるようになりました。

ここで、同じファイルの名前がシートの数だけ出てきてしまいます。
リストボックス１のリストが重複がないようにしたいです。
次に、リストボックス２にシート名を表示させることができません。
リストボックス１から連動するようにしたくて調べているのですがうまくいきません。
今あるシート名を.AddItemで入れてしまうと、シートの数が変わったときに対応できなくなってしまいます。

この2点について教えていただけませんでしょうか？
よろしくお願いいたします。

Private Sub UserForm_Initialize()
    Set一覧取得
    With Me.ListBox1
        .List = ThisWorkbook.Worksheets(1).Range("C1").CurrentRegion.Value
        .ColumnWidths = "100;0;0;0"
    End With
End Sub


Sub Set一覧取得()
    With ThisWorkbook.Worksheets(1).Cells(1)
        'シートの初期化
        .CurrentRegion.ClearContents

        'ファイルのフルパス一覧の取得
        Setファイルのフルパス一覧 .Cells

        'シート名の一覧取得
        Setシートの一覧 .CurrentRegion

        '作業列のクリア
        .EntireColumn.ClearContents
    End With
End Sub

Sub Setファイルのフルパス一覧(ByRef c As Range)
    Const csPath As String = "C:\Users\新しいフォルダー\"
    Dim ixRow As Long
    Dim buf As String

    buf = Dir(csPath & "*.xls?")
    Do Until Len(buf) = 0
        ixRow = ixRow + 1
        c(ixRow, "A").Value = csPath & buf
        buf = Dir()
    Loop
End Sub

Sub Setシートの一覧(ByRef rng As Range)
    Dim c As Range
    Dim wbk As Workbook
    Dim wsh As Worksheet
    Dim ixRow As Long

    For Each c In rng
        Set wbk = Workbooks.Open(c.Value)
        For Each wsh In wbk.Worksheets
            ixRow = ixRow + 1
            With rng(ixRow, "B")
                .Offset(, 1).Value = wbk.Name
                .Offset(, 2).Value = wsh.Name
                .Offset(, 3).Value = wsh.UsedRange.Address(False, False, xlA1, True)
                .Value = Get表示名(.Offset(, 3).Value)
            End With
        Next
        wbk.Close False
    Next
End Sub

Function Get表示名(ByVal sName As String) As String
    Dim i As Long

    i = InStr(sName, "'")
    If i > 0 Then
        Get表示名 = Left(sName, i - 1)
    End If
End Function

Answer 1

＞リストボックス１から連動するようにしたくて調べているのですがうまくいきません。
＞今あるシート名を.AddItemで入れてしまうと、シートの数が変わったときに対応できなくなってしまいます。

一度に色々なことを求めすぎです。

やりたいことが先行して、途中の過程をすっとばして考えられているような印象です。

もともと、一つのユーザーフォームに、二つのリストボックスを用意して、
片方にブック名、片方に選択したブックのシート名のリストを作る仕様でしたっけ？
こちらが勝手に、複数のブックを一つのブックのように扱いたいと勘違いしてました？
いずれにしても、ちょっと直せば、やりたいことが出来ると思ってるなら間違いです。

風が吹く　→　桶屋が儲かる
南無阿弥陀仏と唱える　→　極楽浄土へ行ける
SUM(セル範囲）と唱える　→　合計が出る
リストボックス1を選択する　→　リストボックス2にシートの一覧が出る

いずれも、原因と結果だけで途中の説明が全くされていません。
使う側は、途中を意識する必要はないですが、あなたはプログラムを作る側です。
与えられた原因（情報）から、決まった結果を返す仕組みを作る人です。
AddItemという呪文から一旦離れて、日本語でその結果がでるよう、
手順を説明するところから始めてみてはいかがでしょうか？
プログラミング言語でコードを書けることはさほど重要ではありません。
結果を出す仕組みを考え付くことが一番重要です。
あとは、その手順一つ一つをVBA語に翻訳していくだけでプログラムは完成します。

それが出来たら、先のサンプルを一旦ばらして、
使える部品を使って組み立ててみてください。
呪文の意味や効能が分かってないと組み立てられませんよね？
当然辞書やヘルプなどで調べて行かなければ、どの部品をどのように使えばいいか、
わからないかと思います。

Initializeとはなんぞや？
Worksheetsとは？
InStrとは？
Workbooks.Openとは？
AddItemとは？
オブジェクトとは？
ステートメントとは？
メソッドとは？
プロパティとは？
コレクションとは？
イベントとは？
ForEach ~ Next　と　For ~ Next　の違いとは？

ヘルプなんて読んでないですよね？
なんかすっとばされて、結果だけ求めてる気がします。

オートバイのレーサーになる夢は持ってもいいですが、
自転車にも乗れないのに、レースで優勝するような結果はでないかと思います。
基礎も固めましょう。

ヒント＞＞
第21回.ユーザーフォームの各種イベント

Comments

[o-k]

mattuwanさんアドバイスありがとうございます。
今日は仕事が立て込んでおりますので、
じっくり時間を作って、改めていただいたアドバイスを元に作ってみます。

Answer 2

前回の回答者様のVBAコードを拝見しました。

この間のコードではUserForm内のcase式で対応するシート名を記述していたと思います。

下記のコードは対応表を作成し、リストボックス2にセットする値を動的に変化させるという

記事になります（自動連動は複雑化な為、まずはマスタ表に従うのもよろしいかと思います）。

慣れるまで、一度目を通しておくことをお勧めします。

回答になっていないかもしれませんが、マスタ表を読み込むことでVBAコードの変更をなくす、

複雑化させないなどの工夫として参照いただくとよろしいかと思います。

【エクセルVBA】リストボックスのRowSourceプロパティの値を変動させてみよう！

https://tonari-it.com/excel-vba-userform-listbox-rowsource-2/

Comments

[o-k]

ymakotoさん、前回に引き続きご回答ありがとうございます。

ご紹介いただいたサイト拝見いたしました。
リストボックス1で選んだファイルのシートのみリストボックス2に出てくるようにできればと思っております。

動き的には以下のようにしたいのですが、
これだとシートの数や名前が変わったときに毎回コードを直さなければなりません。

ListBox1_ClickやListBox1_Changeなどやってみましたがうまくいきませんでした。

Private Sub UserForm_Initialize()
With ListBox1
.AddItem "氏名1"
.AddItem "氏名2"
.ListIndex = 0
End With
End Sub

Private Sub ListBox1_Click()
With ListBox2
.Clear
Select Case ListBox1.List(ListBox1.ListIndex)
Case "氏名1"
.AddItem "商品1"
.AddItem "商品2"
.AddItem "商品3"
.AddItem "商品4"
Case "氏名2"
.AddItem "商品11"
.AddItem "商品12"
.AddItem "商品13"
.AddItem "商品14"
.AddItem "商品15"
.AddItem "商品16"
.AddItem "商品17"
End Select
.ListIndex = 0
End With
End Sub

[退会済みユーザー]

回答者でコードの組み方が変わると思いますのでなんとも言えませんが・・。

私は、サンプルのように表を作る。コードをいじらずにシートを読み取って
Listbox2の値を変更する。から検証したほうがよいと思います。

| A列 | B列 | C列
1 氏名1　　商品名1 　XX1.xlsx
2 氏名1　　商品名2 　XX2.xlsx
3 氏名1　　商品名3 　XX3.xlsx

などの対応表のシートを読み込んで処理を行うということです。

余計わかりずらいですかね・・。

このあたりが理解できれば、いいと思います。
まずは、一ファイルのみでVBAで動的にlistbox2にセットできる
方法を模索するとよいかと思います（まず、ファイルを開くことを想定しない）。

→この場合、同一フォルダ内のファイル名を列挙するというよりもマスタシートに登録された
　エクセル表を呼び出して処理を行うという考え方になります。
→今回、提示されているVBAとは方針が違いますのであくまでもサンプルとして理解すると
いう意味です。

[o-k]

ありがとうございます。
一度他のことを忘れてリストボックスに専念したいと思います。

[退会済みユーザー]

それとは別に、エクセルの特定のセルに記述されたエクセルファイル名を開いてA１セルの
値を別シートにコピーして閉じてみるコードを記述してみる。

などパーツごとに別々に動作をシンプルな例で確認することで、リストボックスとの連携で
ファイルを開く、データを転記するなどの処理が整理できると思います。

mainのエクセルから指定のエクセルファイルを開いて閉じる方法
mainのエクセルから指定のエクセルファイルのA1セルの値をmainのエクセルA１に転記して閉じる方法

分けていくとここはループ処理で記述できるなど、いろいろなアイデアが浮かびますし
何より自分の頭を整理できます。

以上　素人なのですが、色々すみません。。

[退会済みユーザー]

ちなみに。。わたしのよく使う手法は、エクセルシートをForm代わりにして検索画面をシート上で作る
パターンが多いです。オートシェイプにVBAコードを割り当てて検索ボタンを作ります。
私は、Userformは面倒なのでよほどのことがない限りあまり使いません。
またエクセルシートの情報を読み込んで処理を行うことがほとんどで
コード内にはあまり情報を詰め込みません。コードはあまり記述せず、関数で抽出してみて
それができれば関数をつかったり、VBAから関数を呼び出すことでシンプルに考えます。

よいサンプルがありましたので、エクセルシート上で連動するリストボックスの作成を
して、この情報から別エクセルを起動してみる。データA１を転記して閉じてみる。
などシンプルにコードを記述してみてはどうでしょうか・・。

※Exlceシートを使った場合（Userformを使わない例）

前の選択で次の選択肢が変わる連動ドロップダウンリスト

https://www.becoolusers.com/excel/indirect-list.html

余計な助言ですが、ぜひ！

[o-k]

ご紹介ありがとうございます。

すみません、ちょっと混乱気味なので少し休んでから作業再開します。

1.ファイルを選ぶ　←　リストボックス1
2.シートを選ぶ　←　リストボックス2
3.該当するシートを開く　←　？
4.そのシートの特定の範囲をコピーする　←　マクロの記録でできそう？
5.別なシートに貼り付ける　←　マクロの記録でできそう？

条件に合ったシートを開くのが一番の難関になるかと思っております。