1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
| Option Explicit
Private Sub lstCity_Click()
With Me
.txtAddress = .lstCity.List(.lstCity.ListIndex, 0)
.txtCountry = .lstCity.List(.lstCity.ListIndex, 1)
.txtCapital = .lstCity.List(.lstCity.ListIndex, 2)
End With
End Sub
Private Sub UserForm_Initialize()
Dim rng As Range, sht As Worksheet
Dim Elem, row As Long, Visible As Boolean
Set rng = ThisWorkbook.Worksheets("Pays").Range("A2:B7")
For Each sht In ThisWorkbook.Worksheets
Select Case LCase(sht.Name)
Case "europe", "afrique"
Set rng = sht.Range("A1").CurrentRegion
' Paramètres
Visible = False ' Affichera ou pas la colonne 1 du ListBox
With lstCity
.ColumnCount = 3: .ColumnWidths = IIf(Visible, "120;50;50", "0;50;50")
For row = 2 To rng.Rows.Count
.AddItem rng.Cells(row, 1).Address(external:=True) ' Adresse
.List(Elem, 1) = rng.Cells(row, 1) ' Ville
.List(Elem, 2) = rng.Cells(row, 2) ' Capitale
Elem = Elem + 1
Next row
End With
End Select
Next sht
End Sub |
Partager