| |
こんな動作をさせたいのかな？
ただ、ListBox1、2共に２列で２列目を非表示にしていますので
後で、氏名を参照する場合は、ListBox2.List(Index,0)を参照すれば出来ます
またBoundColumnは変更していませんので、.Valueで得られる値は通常どおりです
Option Explicit
Private vntIndex() As Variant
Private strSection As String
Private Sub UserForm_Initialize()
Dim i As Long
Dim vntData As Variant
Dim vntSection As Variant
With Worksheets("DB")
vntData = Range(.Cells(3, 3), _
.Cells(65536, 3).End(xlUp)).Value
vntSection = Range(.Cells(3, 20), _
.Cells(UBound(vntData, 1) + 2, 20)).Value
End With
ReDim vntIndex(UBound(vntData, 1) - 1, 2)
For i = 0 To UBound(vntIndex, 1)
vntIndex(i, 0) = True
vntIndex(i, 1) = vntData(i + 1, 1)
vntIndex(i, 2) = vntSection(i + 1, 1)
Next i
With ListBox1
.ColumnCount = 2
.ColumnWidths = CStr(.Width - 4) & ";0"
End With
With ListBox2
.ColumnCount = 2
.ColumnWidths = CStr(.Width - 4) & ";0"
End With
OptionButton1 = True
End Sub
Private Sub CommandButton1_Click()
Dim i As Long
With ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) = True Then
ListBox2.AddItem .List(i, 0)
ListBox2.List(ListBox2.ListCount - 1, 1) _
= .List(i, 1)
vntIndex(.List(i, 1), 0) = False
End If
Next i
For i = .ListCount - 1 To 0 Step -1
If .Selected(i) = True Then
.RemoveItem (i)
End If
Next i
End With
End Sub
Private Sub CommandButton2_Click()
Dim i As Long
With ListBox2
For i = 0 To .ListCount - 1
If .Selected(i) = True Then
vntIndex(.List(i, 1), 0) = True
End If
Next i
MakeList
For i = .ListCount - 1 To 0 Step -1
If .Selected(i) = True Then
.RemoveItem (i)
End If
Next i
End With
End Sub
Private Sub OptionButton1_Click()
strSection = "本社"
MakeList
End Sub
Private Sub OptionButton2_Click()
strSection = "支店"
MakeList
End Sub
Private Sub MakeList()
Dim i As Long
With ListBox1
.Clear
For i = 0 To UBound(vntIndex, 1)
If vntIndex(i, 2) = strSection _
And vntIndex(i, 0) Then
.AddItem vntIndex(i, 1)
.List(.ListCount - 1, 1) = i
End If
Next i
End With
End Sub
|
|
- 03/8/20(水) 21:36 -
