Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


6308 / 13644 ツリー ←次へ | 前へ→

【45793】コンボボックスとリストボックスの連動 ととろ 07/1/14(日) 16:27 質問[未読]
【45797】Re:コンボボックスとリストボックスの連動 Hirofumi 07/1/14(日) 17:32 回答[未読]
【45798】Re:コンボボックスとリストボックスの連動 Kein 07/1/14(日) 17:32 回答[未読]
【45799】Re:コンボボックスとリストボックスの連動 ichinose 07/1/14(日) 17:38 発言[未読]
【45983】Re:コンボボックスとリストボックスの連動 ととろ 07/1/20(土) 18:38 質問[未読]
【45985】Re:コンボボックスとリストボックスの連動 Kein 07/1/20(土) 20:20 回答[未読]
【45987】Re:コンボボックスとリストボックスの連動 Kein 07/1/20(土) 20:26 発言[未読]
【45986】Re:コンボボックスとリストボックスの連動 Hirofumi 07/1/20(土) 20:23 回答[未読]

【45793】コンボボックスとリストボックスの連動
質問  ととろ  - 07/1/14(日) 16:27 -

引用なし
パスワード
   教えていただきたいことがあります。

シート上にA列とB列にデータがあり、ユーザーフォーム上のコンボボックスに、A列を表示させています。
そのコンボボックス(A列)の選択結果により、その値と同じ場合のB列の値をリストボックスに表示させたいのです。

 A    B
 1    1
 2    2
 2    3
 2    4
 3    5
 4    6

コンボボックス(A列)で「2」を選択すると、リストボックスに「2,3,4,5」を表示させる。

Private Sub userform_initialize()

  With ComboBox1
   .RowSource = Range("A1:A10").Address
  End With
   
End Sub

comboBoxに表示させていますが、そこから、先のListboxへの表示の仕方がわかりません。過去ログを調べましたが、ヒントになるようなものがヒットしません。

申し訳ありませんが、教えて頂けないでしょうか?
基本的なことかもしれませんが、糸口が掴めません。

よろしくお願いします。

【45797】Re:コンボボックスとリストボックスの連動
回答  Hirofumi  - 07/1/14(日) 17:32 -

引用なし
パスワード
   Listは、A列で昇順整列されて居る事とします

以下をUserFormのコードモジュールに記述して下さい

Option Explicit

'モジュールレベルの変数を宣言
'(モジュール全域から参照出来る変数)
Private rngList As Range
Private lngRows As Long

Private Sub UserForm_Initialize()

  Dim i As Long
  Dim vntData As Variant
  
  'Listの基準位置を設定
  Set rngList = ActiveSheet.Cells(1, "A")
  With rngList
    'List行数を取得
    lngRows = .Offset(Rows.Count - .Row).End(xlUp).Row - .Row + 1
    If lngRows >= 1 Then
      'ComboBox1にA列データを無重複で登録
      vntData = .Resize(lngRows + 1).Value
      ComboBox1.AddItem vntData(1, 1)
      For i = 2 To lngRows
        If vntData(i - 1, 1) <> vntData(i, 1) Then
          ComboBox1.AddItem vntData(i, 1)
        End If
      Next i
    Else
      lngRows = 0
    End If
  End With
  
End Sub

Private Sub UserForm_Terminate()

  Set rngList = Nothing

End Sub

Private Sub ComboBox1_Change()

  Dim i As Long
  Dim vntData As Variant
  Dim vntKey As Variant
  
  '有効な選択がされた場合
  If ComboBox1.ListIndex > -1 Then
    'ComboBox1の値を変数に取得
    vntKey = ComboBox1.Value
    ListBox1.Clear
    'List行数が1以上なら(Listにデータが有るなら)
    If lngRows > 0 Then
      'A列、B列データを配列に取得
      vntData = rngList.Resize(lngRows, 2).Value
      For i = 1 To lngRows
        'A列の値とComboBox1の値が同じなら
        If CStr(vntData(i, 1)) = vntKey Then
          'ListBox1にB列データを登録
          ListBox1.AddItem vntData(i, 2)
        End If
      Next i
    End If
  End If
        
End Sub

【45798】Re:コンボボックスとリストボックスの連動
回答  Kein  - 07/1/14(日) 17:32 -

引用なし
パスワード
   まず基本的な注意点として、ユーザーフォームがどのシートをアクティブに
しているときに表示されても、特定のシートのセル範囲をリストにできるように、
リスト範囲の設定コードには特定のシートへの参照を付けることを覚えて下さい。
コードは IV列 を作業列にして数式を入れ判定する、というロジックにするとして・・

Private Sub UserForm_Initialize()
  Me.ComboBox1.RowSource = "Sheet1!$A$1:$A$10"
End Sub

Private Sub ComboBox1_Change()
  Dim Num As Long
  Dim C As Range
 
  With ComboBox1
   If .ListIndex = -1 Then Exit Sub
   Num = CLng(.Value)
  End With
  ListBox1.Clear
  With Sheets("Sheet1").Range("IV1:IV10")
   .Formula = "=IF($A1=" & Num & ",$B1)"
   For Each C In .SpecialCells(3, 1)
     ListBox1.AddItem C.Value
   Next
   .ClearContents
  End With
End Sub

【45799】Re:コンボボックスとリストボックスの連動
発言  ichinose  - 07/1/14(日) 17:38 -

引用なし
パスワード
   ▼ととろ さん:
こんにちは。

>シート上にA列とB列にデータがあり、ユーザーフォーム上のコンボボックスに、A列を表示させています。
>そのコンボボックス(A列)の選択結果により、その値と同じ場合のB列の値をリストボックスに表示させたいのです。
>
> A    B
> 1    1
> 2    2
> 2    3
> 2    4
> 3    5
> 4    6

上記のデータが新規ブックのSheet1というシートのA列、
B列に入力されているとします。


ユーザーフォーム(Userform1)には、
   コンボボックス(Combobox1) と
   リストボックス(Listbox1) をそれぞれひとつ配置してください。

Userform1のモジュールに
'============================================================
Private Myrng As Range
'============================================================
Private Sub ComboBox1_Change()
  Dim crng As Range
  Dim comp As Variant
  Dim ans As Variant
  Myrng.Cells(1).Offset(0, 2).Value = ComboBox1.Value
  With ListBox1
    .Clear
    ans = Evaluate("transpose(if(" & Myrng.Address & _
         "=sheet1!c1," & Myrng.Offset(0, 1).Address & _
         ",""" & Chr(&HFF) & """))")
    If TypeName(ans) <> "Variant()" Then
     ans = Array(ans)
     End If
    .List() = Filter(ans, Chr(&HFF), False)
    End With
End Sub
'============================================================
Private Sub UserForm_Initialize()
  Dim doc As Object
  Dim crng As Range
  Set doc = CreateObject("Scripting.Dictionary")
  With Worksheets("sheet1")
    Set Myrng = .Range("a1", .Cells(.Rows.Count, "a").End(xlUp))
    For Each crng In Myrng
     If Not doc.Exists(crng.Value) Then
       doc.Add crng.Value, crng.Value
       End If
     Next
    ComboBox1.List = doc.Keys
    ComboBox1.ListIndex = 0
    End With
End Sub


標準モジュールに
'==================================================
sub main()
  userform1.show
end sub


として、mainを実行してみてください。

ユーザーフォームが表示されますから、
コンボボックスの値を代えてみてください。
選択したデータに対応したB列のデータがリストボックスに表示されます。

尚、Sheet1のセルC1をプログラムが使っていますから、
リザーブしてください

試してみてください。

これ本当は、数字ではないのですよね?

【45983】Re:コンボボックスとリストボックスの連動
質問  ととろ  - 07/1/20(土) 18:38 -

引用なし
パスワード
   返事が遅くなりまして申し訳ありません。
なんとか、うまくできました。
ありがとうございました。

そこで、再度教えていただきたいのですが、
リストボックスに表示されたリストを複数選択可能なようにして、
複数選択した場合の値を、シート1のセル範囲(A1:B5)に表示させたいのです。

すみませんが、教えていただけませんでしょうか?

【45985】Re:コンボボックスとリストボックスの連動
回答  Kein  - 07/1/20(土) 20:20 -

引用なし
パスワード
   シート1のA1:B5に入力するなら、コンボボックスとリストボックスが参照する
リストは、仮に Sheet2 ということにします。フォームモジュールのコードは

Private Sub UserForm_Initialize()
  Dim Ary() As Variant
  Dim i As Integer, j As Integer
 
  ReDim Ary(0): Ary(0) = Empty
  With Sheets("Sheet2")
   For i = 1 To 10
     If IsError(Application _
     .Match(.Cells(i, 1).Value, Ary, 0)) Then
      ReDim Preserve Ary(j)
      Ary(j) = .Cells(i, 1).Value: j = j + 1
     End If
   Next i
  End With
  Me.ComboBox1.List = Ary: Erase Ary
  Me.ListBox1.MultiSelect = fmMultiSelectMulti
End Sub

Private Sub ComboBox1_Change()
  Dim Num As Long
  Dim C As Range
 
  With ComboBox1
   If .ListIndex = -1 Then Exit Sub
   Num = CLng(.Value)
  End With
  ListBox1.Clear
  With Sheets("Sheet2").Range("IV1:IV10")
   .Formula = "=IF($A1=" & Num & ",$B1)"
   On Error Resume Next
   .SpecialCells(3, 4).ClearContents
   On Error GoTo 0
   For Each C In .SpecialCells(3)
     ListBox1.AddItem C.Value
   Next
   .ClearContents
  End With
End Sub

Private Sub CommandButton1_Click()
  Dim i As Integer, j As Integer
 
  Sheets("Sheet1").Range("A1:B5").ClearContents
  With ListBox1
   For i = 0 To .ListCount - 1
     If .Selected(i) Then
      j = j + 1
      Sheets("Sheet1").Range("A1:B5") _
      .Cells(j).Value = .List(i)
     End If
   Next i
  End With
End Sub

で、出来ると思います。

【45986】Re:コンボボックスとリストボックスの連動
回答  Hirofumi  - 07/1/20(土) 20:23 -

引用なし
パスワード
   >リストボックスに表示されたリストを複数選択可能なようにして、
>複数選択した場合の値を、シート1のセル範囲(A1:B5)に表示させたいのです。

シート1のセル範囲(A1:B5)にどう言う形で出力したいのか?
CommandButton1を押す事によって、A1、A2、A3・・B1、B2・・と出力されます

Private Sub CommandButton1_Click()

  '最大出力行数を設定
  Const clngMaxRows As Long = 5
  
  Dim i As Long
  Dim j As Long
  Dim lngColumns As Long
  
  '書き込む先頭セル位置を指定
  With Worksheets("Sheet1").Cells(1, "A")
    '書き込んで有る列数を取得
    lngColumns = .Offset(, Columns.Count _
        - .Column).End(xlToLeft).Column - .Column + 1
    '範囲をクリア
    .Resize(clngMaxRows, lngColumns).ClearContents
    'List全てに就いて繰り返し
    For i = 0 To ListBox1.ListCount - 1
      '選択されているなら
      If ListBox1.Selected(i) = True Then
        '値を出力
        .Offset(j Mod clngMaxRows, _
            j \ clngMaxRows).Value = ListBox1.List(i, 0)
        '書き込み位置を更新
        j = j + 1
        '選択を解除
        ListBox1.Selected(i) = False
      End If
    Next i
  End With
    
End Sub

【45987】Re:コンボボックスとリストボックスの連動
発言  Kein  - 07/1/20(土) 20:26 -

引用なし
パスワード
   リストボックスを MultiSelect にした場合、コマンドボタンを一つ配置しておき、
選択したいリストを全て選択してから、ボタンを押すことで選択結果を取得する
ようにします。それが一般的なやり方であると考え、提示したコードもそのように
していますが、ボタンを追加したくなければ、リストボックスのダブルクリック
イベントなども代用になるかも知れません。それはそちらでテストして下さい。

6308 / 13644 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free