|
シート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
で、出来ると思います。
|
|