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

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




         Veri doğrulama listelerini birçok yerde kullanıyoruz. Hem görünüm açısından , hem de kullanışlılık açısından faydalı bir araç Excel Veri Doğrulama özelliği . Basit Veri Doğrulama Liste özelliği 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ı genelde gözardı edilir.
         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 harf sıralamasına sokma önemli yöntemlerdir.

Bir Excel şablonu ve bu şablonda VBA kodlar ile benzersiz (unique) alfabetik sıralanmış değerler içeren Veri Doğrulama Listeleri oluşturduk.

        💡 Çalışma kitabında ; Anasayfa ve Urunler isimlerinde iki sayfa bulunmakta.
Urunler sayfasındaki B sütunundaki verileri (ürünleri) , Anasayfa 'nın A sütununda 2. ve 41.satır arasında -A2:A41 hücreleri-  Veri Doğrulama Listeleri oluşturarak listeledik. 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ü açtık.
✔️ BuÇalışmaKitabı 'na sağ tıklayıp açılır menüden Insert _ Module seçtik.


✔️ Oluşan modüle aşağıdaki kodları ekledik.
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 şablonun ö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.

excel veri doğrulama listesi benzersiz sıralı değerler içerir

No comments:

Post a Comment