Genel Bakış
ListFillRange (ControlFormat.ListFillRange özelliği), bir form denetimi olan liste kutusunun ya da açılan liste kutusunun (birleşik kutu) öğelerini hangi hücre aralığından alacağını belirleyen, okunabilir ve yazılabilir bir özelliktir. Değer metin (String) olarak verilir; örneğin "A2:A7" ya da bir tanımlı ad. Böylece denetimi elle doldurmak yerine çalışma sayfasındaki bir listeye bağlayabilirsiniz.
VBA ile bu denetimleri Shapes.AddFormControl yöntemiyle oluşturabilirsiniz. Tür için xlDropDown (açılan liste, değeri 2) ya da xlListBox (liste kutusu, değeri 6) sabiti kullanılır. ActiveX denetimlerinde (OLEObject) ve UserForm denetimlerinde farklı özellikler kullanılır; UserForm kutularında kaynak için RowSource özelliği vardır, bu sayfadaki ControlFormat.ListFillRange onlar için geçerli değildir.
İlgili özelliklerden ListIndex seçili öğenin sırasını (hiçbiri seçili değilse 0), ListCount öğe sayısını, DropDownLines açılan listede görünen satır sayısını, LinkedCell ise seçilen öğenin sırasının yazılacağı hücreyi belirler.
Makro çalışınca E2 konumunda, altı şehri listeleyen bir açılan liste kutusu oluşur. C2 hücresine denetimin döndürdüğü kaynak adresi (A2:A7), C3 hücresine öğe sayısı (6), C4 hücresine ise ListIndex ile seçilen üçüncü öğe (İzmir) yazılır. Kaynak adresini okurken Excel'in sayfa adı ya da mutlak başvuru ekleyebileceğine dikkat edin.
Liste kutusu sekiz ürünü gösterir. D2 hücresinde denetimin kaynak olarak bildirdiği değer (UrunListesi), D3 hücresinde öğe sayısı (8) yer alır. Üçüncü öğe seçildiği için D5 hücresine seçilen ürün (Monitör), D4 hücresine ise bu ürünün stoğu (38) yazılır. Ad kullanmanın yararı, kaynağı daha sonra Ad Yöneticisi'nden genişletebilmenizdir; denetim yeniden atanmadan da aynı adı kullanır.
İlk atamada liste beş öğe içerir; D2 hücresi bu sayıyı (5) gösterir. Makro A7 hücresine yeni personeli (Seda Yıldız) yazdıktan sonra ListFillRange özelliğini yeniden atadığı için D3 hücresindeki öğe sayısı (6) bir artar. D4 hücresi denetimin bildirdiği güncel kaynak adresini (A2:A7) gösterir. Kaynağı yeniden atamasaydınız A7 aralığın dışında kalacak ve açılan listede görünmeyecekti.
Söz dizimi
Özellik, bir Shape nesnesinin ControlFormat alt nesnesi üzerinden kullanılır:
VBA
8 satır
' Okuma
deger = ifade.ListFillRange
' Yazma
ifade.ListFillRange = "A2:A7"
' Tipik kullanım
Worksheets("Sayfa1").Shapes("cmbSehir").ControlFormat.ListFillRange = "A2:A7"- ifade: Zorunlu; bir liste kutusu ya da açılan liste kutusu form denetiminin ControlFormat nesnesi.
- Değer: String; denetimin öğelerini alacağı aralığın adresi ("A2:A7" gibi), sayfa adıyla nitelenmiş adres ya da aralığa verilmiş tanımlı ad. Bir Range nesnesi değil, metin verilmelidir.
- Dönüş değeri: Okunduğunda geçerli kaynak aralığını metin olarak döndürür. Adresin sayfa adı ve mutlak başvuru içerip içermediği Excel sürümüne göre değişebileceğinden, döndürülen metnin biçimine bağımlı kod yazmayın.
- Varsayılan: Kaynak atanmamış denetimde özellik boş metindir.
Hangi denetimlerde kullanılır
Özellik, Geliştirici > Denetimler > Ekle > Form Denetimleri (Developer > Controls > Insert > Form Controls) altındaki Liste Kutusu (List Box) ve Açılan Liste Kutusu (Combo Box) için geçerlidir. Aynı ayar arayüzde denetime sağ tıklayıp Denetimi Biçimlendir > Denetim > Giriş aralığı (Format Control > Control > Input range) kutusuyla yapılır.VBA ile bu denetimleri Shapes.AddFormControl yöntemiyle oluşturabilirsiniz. Tür için xlDropDown (açılan liste, değeri 2) ya da xlListBox (liste kutusu, değeri 6) sabiti kullanılır. ActiveX denetimlerinde (OLEObject) ve UserForm denetimlerinde farklı özellikler kullanılır; UserForm kutularında kaynak için RowSource özelliği vardır, bu sayfadaki ControlFormat.ListFillRange onlar için geçerli değildir.
Kaynak aralığı yazım biçimleri
- Düz adres: "A2:A7" yazımı denetimin bulunduğu sayfadaki aralığı gösterir.
- Sayfa adıyla adres: Başka bir sayfadaki listeyi kullanacaksanız sayfa adını da yazın; adda boşluk varsa tek tırnak kullanın.
- Tanımlı ad: "UrunListesi" gibi bir ad vermek, kaynağın yerini kod dışında (Formüller > Tanımlı Adlar > Ad Yöneticisi (Formulas > Defined Names > Name Manager)) yönetmenizi sağlar.
- Dinamik uzunluk: Son dolu satırı bulup "A2:A" & sonSatir biçiminde birleştirerek liste uzunluğunu veriye uydurabilirsiniz.
İlgili özelliklerden ListIndex seçili öğenin sırasını (hiçbiri seçili değilse 0), ListCount öğe sayısını, DropDownLines açılan listede görünen satır sayısını, LinkedCell ise seçilen öğenin sırasının yazılacağı hücreyi belirler.
1. Açılan listeyi şehir listesine bağlama
A2:A7 aralığında altı şehir var. Aşağıdaki kodu standart bir modüle yapıştırın. Makro, E2 hücresinin üzerine bir açılan liste kutusu ekler, kaynağını A2:A7 olarak belirler, üçüncü öğeyi seçer ve bilgileri C sütununa yazar.
VBA
28 satır
Option Explicit
Sub SehirListesiBagla()
Dim ws As Worksheet
Dim shp As Shape
Set ws = ThisWorkbook.Worksheets("Şehir Listesi")
' Önceki çalıştırmadan kalan denetimi sil
On Error Resume Next
ws.Shapes("cmbSehir").Delete
On Error GoTo 0
' E2 hücresinin üzerine açılan liste kutusu ekle
Set shp = ws.Shapes.AddFormControl(xlDropDown, ws.Range("E2").Left, ws.Range("E2").Top, 120, 20)
shp.Name = "cmbSehir"
With shp.ControlFormat
.ListFillRange = "A2:A7" ' öğelerin geldiği aralık
.DropDownLines = 6 ' açılışta görünen satır sayısı
.ListIndex = 3 ' üçüncü öğeyi seç
End With
' Sonuçları sayfaya yaz
ws.Range("C2").Value = shp.ControlFormat.ListFillRange
ws.Range("C3").Value = shp.ControlFormat.ListCount
ws.Range("C4").Value = ws.Range("A2:A7").Cells(shp.ControlFormat.ListIndex).Value
End SubListFillRange
Formülünü incelemek için bir hücre seçin.| A | B | C | |
|---|---|---|---|
| 1 | Şehir | Açıklama | Sonuç |
| 2 | İstanbul | Liste kaynağı | A2:A7 |
| 3 | Ankara | Öğe sayısı | 6 |
| 4 | İzmir | Seçilen öğe (3. sıra) | İzmir |
| 5 | Bursa | ||
| 6 | Antalya | ||
| 7 | Konya |
Şehir ListesiExcelDepo v1.0.0
2. Tanımlı ad üzerinden liste kutusu doldurma
A2:A9 aralığında ürün adları, B sütununda stok adetleri var. Kod standart modüle yapıştırılır. Makro önce aralığa UrunListesi adını verir, F2 hücresinin üzerine bir liste kutusu ekler ve ListFillRange özelliğine adı metin olarak atar. Ardından seçilen ürünü ve stoğunu D sütununa yazar.
VBA
35 satır
Option Explicit
Sub UrunListesiniAdlaBagla()
Dim ws As Worksheet
Dim shp As Shape
Dim kaynak As Range
Dim sira As Long
Set ws = ThisWorkbook.Worksheets("Ürün Stok")
' Kaynak aralığa ad ver
ThisWorkbook.Names.Add Name:="UrunListesi", _
RefersTo:="='" & ws.Name & "'!$A$2:$A$9"
' Eski denetim varsa sil
On Error Resume Next
ws.Shapes("lstUrun").Delete
On Error GoTo 0
' F2 hücresinin üzerine liste kutusu ekle
Set shp = ws.Shapes.AddFormControl(xlListBox, ws.Range("F2").Left, ws.Range("F2").Top, 120, 100)
shp.Name = "lstUrun"
' Kaynağı ad olarak ver (metin)
shp.ControlFormat.ListFillRange = "UrunListesi"
shp.ControlFormat.ListIndex = 3
sira = shp.ControlFormat.ListIndex
Set kaynak = ThisWorkbook.Names("UrunListesi").RefersToRange
ws.Range("D2").Value = shp.ControlFormat.ListFillRange
ws.Range("D3").Value = shp.ControlFormat.ListCount
ws.Range("D4").Value = kaynak.Cells(sira).Offset(0, 1).Value
ws.Range("D5").Value = kaynak.Cells(sira).Value
End SubListFillRange
Formülünü incelemek için bir hücre seçin.| A | B | C | D | |
|---|---|---|---|---|
| 1 | Ürün | Stok | Kontrol | Sonuç |
| 2 | Laptop | 24 | Kaynak | UrunListesi |
| 3 | Yazıcı | 12 | Öğe sayısı | 8 |
| 4 | Monitör | 38 | Seçilen ürünün stoğu | 38 |
| 5 | Klavye | 85 | Seçilen ürün | Monitör |
| 6 | Fare | 120 | ||
| 7 | Tablet | 17 | ||
| 8 | Kulaklık | 54 | ||
| 9 | Webcam | 31 |
Ürün StokExcelDepo v1.0.0
3. Kaynak aralığını son satıra göre güncelleme
A2:A6 aralığında beş personel adı var, A7 boş. Kodu standart modüle yapıştırın. Makro listeyi dolu satır sayısına göre bağlar, A7 satırına yeni bir personel ekler ve ListFillRange özelliğini son satıra göre yeniden atayarak öğe sayısının arttığını gösterir.
VBA
31 satır
Option Explicit
Sub PersonelListesiniGuncelle()
Dim ws As Worksheet
Dim shp As Shape
Dim sonSatir As Long
Set ws = ThisWorkbook.Worksheets("Personel")
On Error Resume Next
ws.Shapes("cmbPersonel").Delete
On Error GoTo 0
Set shp = ws.Shapes.AddFormControl(xlDropDown, ws.Range("F2").Left, ws.Range("F2").Top, 140, 20)
shp.Name = "cmbPersonel"
' 1. adım: mevcut dolu satırlara göre kaynağı ver
sonSatir = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
shp.ControlFormat.ListFillRange = "A2:A" & sonSatir
ws.Range("D2").Value = shp.ControlFormat.ListCount
' Yeni personel ekle (A2:A6 aralığının dışında)
ws.Range("A7").Value = "Seda Yıldız"
ws.Range("B7").Value = "Satış"
' 2. adım: kaynağı yeniden ata
sonSatir = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
shp.ControlFormat.ListFillRange = "A2:A" & sonSatir
ws.Range("D3").Value = shp.ControlFormat.ListCount
ws.Range("D4").Value = shp.ControlFormat.ListFillRange
End SubListFillRange
Formülünü incelemek için bir hücre seçin.| A | B | C | D | |
|---|---|---|---|---|
| 1 | Personel | Departman | Kontrol | Sonuç |
| 2 | Ayşe Demir | Muhasebe | İlk öğe sayısı | 5 |
| 3 | Mehmet Kaya | Satış | Yeni öğe sayısı | 6 |
| 4 | Zeynep Arslan | İnsan Kaynakları | Kaynak aralığı | A2:A7 |
| 5 | Ali Çelik | Lojistik | ||
| 6 | Fatma Yılmaz | Satış | ||
| 7 | Seda Yıldız | Satış |
PersonelExcelDepo v1.0.0
Kullanırken dikkat edin
- Değer metin olmalıdır: ListFillRange bir Range nesnesi kabul etmez; Set ile aralık nesnesi atamak hata verir. Adresi "A2:A7" gibi metin olarak ya da Range.Address çıktısıyla verin.
- Aralığın dışına eklenen satırlar listeye girmez: Kaynak aralığın içindeki hücre değişiklikleri listeye yansır; ama aralığın hemen altına eklenen yeni satırlar otomatik katılmaz. Çözüm olarak son dolu satırı bulup özelliği yeniden atayın ya da kaynağı genişleyen bir tanımlı ad ile verin.
- Boş hücreler boş öğe olur: Kaynak aralığında boş hücre varsa liste bu hücreler için boş satır gösterir. Aralığı gereğinden büyük vermeyin; son satırı ve ListCount değerini kontrol edin.
- Sayfa adı ve ad yönetimi: Kaynak başka bir sayfadaysa sayfa adını da yazın ve adda boşluk varsa tek tırnak kullanın. Sayfa adı değişirse metin olarak yazdığınız adres geçersiz kalabilir; tanımlı ad bu açıdan daha dayanıklıdır.
- Denetim türünü karıştırmayın: Özellik form denetimlerinin ControlFormat nesnesine aittir. ActiveX denetimlerinde ve UserForm kutularında farklı özellikler (ListFillRange özelliğinin OLEObject karşılığı, RowSource) kullanılır. Ayrıca aynı denetimi hem kaynak aralığıyla hem de AddItem ile doldurmaya çalışmayın; yalnız birini seçin.
- Makro tekrar çalışınca denetim çoğalır: AddFormControl her çalıştırmada yeni bir denetim ekler. Örneklerdeki gibi denetime sabit bir ad verip eskisini silerek tekrarlı çalıştırmada üst üste denetim oluşmasını önleyin.
- İlgili konular:
- LinkedCell: Seçilen öğenin sırasının yazıldığı hücreyi belirler.
- ListIndex ve ListCount: Seçili öğenin sırası ve öğe sayısı.
- DropDownLines: Açılan listede görünen satır sayısı.
- Shapes.AddFormControl: Form denetimi oluşturan yöntem.
- Names.Add: Kaynak aralığa tanımlı ad veren yöntem.
- Veri Doğrulama (Data Validation) listesi: Hücre içinde açılan liste için denetimsiz alternatif.
- İNDİS ve KAÇINCI: Seçilen sıraya göre listeden değer getirmek için kullanılan işlevler; KAYDIR ise genişleyen kaynak adı tanımlamada işe yarar.
