إضافة عناصر جديدة إلى قائمة منسدلة وتحديثها تلقائياً Automatically Add Data To Validation List

السلام عليكم ورحمة الله وبركاته

أقدم لكم موضوع فيه فوائد متعددة ، حيث سنتعلم كيفية تسمية نطاق ديناميكي ، وإنشاء قائمة منسدلة ، كما ستتم عملية الإضافة التلقائية للقائمة المنسدلة عن طريق الكود.

بفرض أن لديك بعض الأسماء في النطاق A1:A5 في ورقة العمل المسماة Sheet1 ، سنقوم أولاً بإنشاء نطاق مسمى.

إنشاء نطاق مسمى Named Range لنطاق ديناميكي Dynamic Range :

قم بالذهاب للتبويب Formulas ثم Name Manager ثم انقر الأمر New كما بالصورة التالية :

ثم في الحقل المسمى Name سنقوم بوضع اسم للنطاق وهو MyNames ، وفي الحقل المسمى Refers To سنقوم بوضع المعادلة كما في الصورة ، حيث تم استخدام الدالة Offset للإزاحة ونقطة البداية هي الخلية A1 ويمتد النطاق بامتداد عدد الخلايا الغير فارغة في العمود الأول ، وأخيراً قم بالنقر على OK ثم النقر على Close ، وبهذا تم تسمية النطاق الديناميكي.

ننتقل للنقطة الثانية ألا وهي إنشاء قائمة منسدلة :

حدد الخلية المراد عمل قائمة منسدلة لها ، ولتكن الخلية D1 على سبيل المثال كما بالملف المرفق ثم قم بالذهاب إلى التبويب Data ثم انقر الأمر Data Validation ثم من الحقل المسمى Allow اختر الخيار List ، وفي حقل الـ Source ضع مؤشر الماوس ثم اضغط F3 من لوحة المفاتيح والذي يتيح لك اختيار النطاق المراد عمل قائمة منسدلة له كما بالصورة :

الآن من نفس النافذة انتقل للتبويب Error Alert وأزل علامة الصح بجانب الخيار

 Show error alert after invalid data is entered

وأخيراً اضغط أوك

وأخيراً ننتقل لآخر جزئية ألا وهي وضع الكود التالي في حدث ورقة العمل ، كليك يمين على ورقة العمل ثم View Code ثم ضع الكود التالي :

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lReply As Long
    
    If Target.Cells.Count > 1 Then Exit Sub
    
    If Target.Address = "$D$1" Then
        If IsEmpty(Target) Then Exit Sub
        
        If WorksheetFunction.CountIf(Range("MyNames"), Target) = 0 Then
            lReply = MsgBox("Add " & Target & " To List", vbYesNo + vbQuestion)
            If lReply = vbYes Then
                Range("MyNames").Cells(Range("MyNames").Rows.Count + 1, 1) = Target
            End If
        End If
        
        Target.Activate
    End If
End Sub

لتطبيق الكود قم بكتابة اسم جديد غير موجود بالقائمة وليكن Hany ثم اضغط Enter ، ستلاحظ ظهور رسالة تطالب بإضافة الاسم للقائمة ، قم بالضغط على Enter مرة أخرى ليتم إضافة الاسم بعد آخر خلية بها بيانات في العمود الأول ، كما ستضاف بشكل تلقائي للقائمة المنسدلة إذ أن النطاق المسند إليها نطاق ديناميكي.

أما إذا كتبت اسم موجود من قبل في القائمة فلن تظهر الرسالة ولن يضاف الاسم.

تحميل الملف من هنا

إعداد / ياسر خليل أبو البراء