Kapat

Excel Benzersiz Ve Sıralı Değerler İçeren Veri Doğrulama Listesi

excel  listbox context menu Excel VBA Veri Doğrulama
Listeleri

Veri doğrulama yöntemi ile oluşturulan listeleri Excel çalışma sayfalarında birçok yerde kullanırız. Hem görünüm açısından , hem de kullanışlılık açısından faydalı bir özelliktir ; Excel Veri Doğrulama Listeleri. Basit Veri Doğrulama Listeleri Ad Tanımlama yöntemi , manuel olarak verileri girerek yada formüller ile oluşturulur. Bu tip listede benzersiz değerler ve harf sıralaması gözardı edilir,dikkate alınmaz.Daha kullanışlı , hızlı sonuçlar alınması açısından tekil değerlerden yani verinin alındığı sütunda aynı iki değer varsa örneğin ;  B:12 hücresinde kitap , B:25 hücresinde kitap varsa listelemede sadece birini almak ve listenin hepsini A dan Z ye alfabetik harf sıralamasına sokma önemli yöntemlerdir.

Bu şablonda, VBA kodlar ile benzersiz (unique) alfabetik sıralanmış değerler içeren Veri Doğrulama Listeleri (Data Validation Lists) oluşturduk.

💡 Çalışma kitabımızda ; Anasayfa ve Urunler isimlerinde iki sayfa bulunmaktadır.
Urunler sayfası B sütunundaki verileri (ürünleri) , Anasayfa ‘nın A sütununda 2. ve 41.satırları arasında -A2:A41 hücreleri-  Veri Doğrulama Listeleri oluşturarak listeledik. VBA For-Next döngüsü ile A2:A41 hücrelerine tekil değerler içeren , alfabetik sıralı listelerimizi ekledik.

Bunun için ;
✔️ Alt + F11 tuşlarına basarak VBA Editör Penceresi’ni açtık.
✔️ BuÇalışmaKitabı ‘na sağ tıklayıp açılır menüden Insert _ Module seçtik. excel vba

excel modül ekle

✔️ Eklediğimiz modüle aşağıdaki VBA kodları ekledik : excel vba kodlar

Private Sub Worksheet_Change(ByVal Target As Range)
Dim dizi As Object, Veri As Range, Son As Long, x As Byte

If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub

Application.ScreenUpdating = False
Application.EnableEvents = False

    On Error Resume Next
    Set dizi = CreateObject("System.Collections.ArrayList")
    Son = Sheets("Urunler").Cells(Rows.Count, 2).End(3).Row
    For Each Veri In Sheets("Urunler").Range("B2:B" & Son)
    Veri = Replace(Veri, ",", ".")
    If dizi.Contains(Replace(Replace(Veri.Value, "i", "i"), "i", "i")) = False Then
    dizi.Add Replace(Replace(Veri.Value, "i", "i"), "i", "i")
        End If
     Next
    dizi.Sort
 
  For x = 2 To 41
   Sheets("Anasayfa").Cells(x, 1).Validation.Delete
   Sheets("Anasayfa").Cells(x, 1).Validation.Add Type:=xlValidateList,         Formula1:=Join(dizi.ToArray, ",")
 Next

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Ayrıca hazırladığımız Excel Veri Doğrulama Listeleri’nin önemli bir özelliği de ; Urunler sayfasında bir değişiklik olduğunda yani yeni ürün eklendiğinde veya silindiğinde otomatikman yapılan işlemin Anasayfa daki listeye yansımasıdır. Bunu sağlamak için kodlarımızı Urunler sayfasının Worksheet_Change metoduna ekledik :
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B:B")) Is Nothing Then
Exit Sub
Else
Call Benzersiz_Alfabetik_Liste
End If
End Sub

🔧 Veri = Replace(Veri, ",", ".")  kodları ile ürün değerleri içerisinde “,” (virgül) varsa bunu “.” nokta ile değiştirir. Çünkü , virgül karakteri dizide kullanılmaktadır ve Excel virgülü gördüğünde yeni bir liste öğesi varsayımı ile hareket etmektedir. Bu da istenmeyen bir durumdur.

Sonuç olarak ; alfabetik olarak sıralı ,benzersiz değerlere sahip Veri Doğrulama Listeleri oluşturduk.

excel benzersiz alfabetik sıralı veri doğrulama liste

📥 Örnek dosyayı buradan indirebilirsiniz.