एक्सेल में VBA के साथ डेटा सत्यापन सूची के लिए नामांकित श्रेणी का उपयोग कैसे करें

यदि आप डेटा सत्यापन . के लिए नामित श्रेणी का उपयोग करने के सबसे आसान तरीकों की तलाश कर रहे हैं एक्सेल में सूची VBA , तो आपको यह लेख उपयोगी लगेगा। नामांकित श्रेणियां आसानी से ड्रॉपडाउन सूची बनाने के लिए डेटा सत्यापन सूत्र में उपयोग करने के लिए उपयोगी होती हैं और कुछ VBA की सहायता से इस कार्य को सुपर आसान बनाया जा सकता है कोड।
तो, आइए डेटा सत्यापन सूची में नामित श्रेणियों का उपयोग करने के तरीकों का पता लगाने के लिए अपना मुख्य लेख शुरू करें।

कार्यपुस्तिका डाउनलोड करें

VBA के साथ Excel में डेटा सत्यापन सूची के लिए नामांकित श्रेणी का उपयोग करने के 4 तरीके

यहां, हमारे पास निम्नलिखित डेटासेट हैं जिनमें कुछ उत्पादों के रिकॉर्ड और उनके संबंधित विक्रेता की सूचियां हैं। इस डेटासेट का उपयोग करके हम अलग-अलग VBA . के साथ अलग-अलग तरीके दिखाने का प्रयास करेंगे डेटा सत्यापन सूची में नामित श्रेणियों का उपयोग करने के लिए कोड।

हमने Microsoft Excel 365 . का उपयोग किया है यहां संस्करण, आप अपनी सुविधा के अनुसार किसी अन्य संस्करण का उपयोग कर सकते हैं।

विधि-1 :ड्रॉपडाउन सूची बनाने के लिए डेटा सत्यापन में नामांकित श्रेणी का उपयोग करना

यहां, हमने फलों . की श्रेणी का नाम दिया है फलों . के साथ कॉलम और एक VBA . का उपयोग करना कोड हम सेल D6 . में एक ड्रॉपडाउन सूची बनाएंगे ।

चरण-01 :
➤ डेवलपर . पर जाएं टैब>> विजुअल बेसिक विकल्प।

फिर, विजुअल बेसिक संपादक खुल जाएगा।
➤ सम्मिलित करें . पर जाएं टैब>> मॉड्यूल विकल्प।

उसके बाद, एक मॉड्यूल बनाया जाएगा।

चरण-02 :
➤ निम्नलिखित कोड लिखें

Sub Datavalidation1()

Range("D6").Validation.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, Formula1:="=Fruits"

End Sub

यहां, सत्यापन सेल में जोड़ा जाएगा D6 , xlValidateList एक ड्रॉपडाउन सूची बनाने के लिए है और सूत्र का उपयोग श्रेणी के नाम के रूप में किया जाता है “=फल” ।

➤ F5➤ दबाएं और बाद में सेल D6 . के ड्रॉपडाउन सिंबल पर क्लिक करें .
फिर, आपको फलों की सूची मिलेगी और सूची में से कोई एक आइटम चुनें जैसे चेरी ।

अंत में, हम सेल D6 . में अपना चयनित आइटम प्राप्त कर रहे हैं ।

और पढ़ें:Excel में तालिका से डेटा सत्यापन सूची कैसे बनाएं (3 तरीके)

विधि-2 :VBA कोड के साथ नामांकित श्रेणी और डेटा सत्यापन सूची जोड़ना

हम यहां मैन्युअल रूप से कोई नामित श्रेणी नहीं बनाएंगे, बल्कि एक साधारण VBA कोड एक नामित श्रेणी बनाएगा, और उसके बाद, इसका उपयोग करके हम अंततः सेल D6 में ड्रॉपडाउन सूची प्राप्त करेंगे ।

कदम :
➤ चरण-01 . का पालन करें की विधि-1 .
➤ निम्नलिखित कोड लिखें

Sub Datavalidation2()

ActiveWorkbook.Names.Add Name:="Fruit", _
RefersTo:=ThisWorkbook.Worksheets("Add").Range("B4:B10")
Range("D6").Validation.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, Formula1:="=Fruit"

End Sub

सबसे पहले, यह नाम जोड़ देगा फल “B4:B10” . की सीमा तक कार्यपत्रक का जोड़ें .
फिर, हम सत्यापन जोड़ देंगे सेल को D6 , xlValidateList एक ड्रॉपडाउन सूची बनाने के लिए है और सूत्र का उपयोग श्रेणी के नाम के रूप में किया जाता है “=फल” ।

➤ F5➤ दबाएं , फिर, वर्कशीट पर जाएं और सेल D6 . के ड्रॉपडाउन सिंबल पर क्लिक करें .
बाद में, आपको फलों की सूची मिलेगी और सूची में से कोई एक आइटम चुनें जैसे ब्लूबेरी ।

तो, हमें हमारी वांछित वस्तु मिल गई है ब्लूबेरी सूची से और इसके अलावा, हम फलों के लिए हमारी बनाई गई नामित श्रेणी देख सकते हैं।

संबंधित सामग्री:एक्सेल में एकाधिक चयन के साथ डेटा सत्यापन ड्रॉप-डाउन सूची बनाएं

समान रीडिंग:

• एक्सेल में स्वत:पूर्ण डेटा सत्यापन ड्रॉप डाउन सूची (2 तरीके)
• फ़िल्टर के साथ एक्सेल डेटा सत्यापन ड्रॉप डाउन सूची (2 उदाहरण)
• एक्सेल वीबीए (मैक्रो और यूजरफॉर्म) के साथ डेटा सत्यापन सूची में डिफ़ॉल्ट मान
• Excel में एकाधिक मानदंडों के लिए कस्टम डेटा सत्यापन लागू करें (4 उदाहरण)
• केवल एक्सेल डेटा सत्यापन अल्फ़ान्यूमेरिक (कस्टम फ़ॉर्मूला का उपयोग करके)

विधि-3 :एक्सेल VBA का उपयोग करके नामांकित श्रेणी के साथ डेटा सत्यापन सूची को अपडेट करना

मान लीजिए, हमारे पास सेल D6 . में निम्न ड्रॉपडाउन सूची है , जो निश्चित डेटासेट के लिए ठीक काम करता है।

लेकिन, अगर हम एक अतिरिक्त सब्जी सलाद मिलाते हैं तो यह ड्रॉपडाउन सूची में दिखाई नहीं देगा जिसका अर्थ है कि इस मामले में हमारी ड्रॉपडाउन सूची स्वचालित रूप से अपडेट नहीं होती है।

सूची को जल्दी और स्वचालित रूप से अपडेट करने के लिए आप इस पद्धति का पालन कर सकते हैं।

3.1:अद्यतन नामित श्रेणी बनाना

सबसे पहले, हमें कॉलम . की श्रेणी के लिए एक नाम जोड़ना होगा बी इस तरह से यह स्वचालित रूप से नए जोड़े गए आइटम को इस नाम पर ले जाएगा।
➤ सूत्रों . पर जाएं टैब>> परिभाषित नाम समूह>> नाम प्रबंधक विकल्प।

फिर, नाम प्रबंधक डायलॉग बॉक्स खुल जाएगा।
➤ नया . पर क्लिक करें विकल्प।

उसके बाद, नाम संपादित करें विज़ार्ड पॉप अप होगा।
➤ नीचे लिखें सब्जियां नाम . में बॉक्स और निम्न सूत्र में संदर्भित करता है बॉक्स और अंत में ठीक press दबाएं ।

यहां, अपडेट करें! शीट का नाम है, $B$4 वह संदर्भ कक्ष है जिससे हम स्थानांतरित करना चाहते हैं, 0 पंक्तियों . के लिए और कॉलम तर्कों का अर्थ है कि यह अपने संदर्भ या प्रारंभिक स्थिति में रहेगा।
COUNTA स्तंभ B . में किसी भी प्रकार के मान वाले कक्षों की संख्या की गणना करेगा और फिर 2 B1 . में डेटासेट के शीर्षक के कारण घटाया जाएगा और B3 . में कॉलम का हेडर . तो, आपको केवल सब्जियों वाली कोशिकाओं की संख्या ही मिलेगी।
यह संख्या प्रारंभिक स्थिति से वापसी संदर्भ होगी और OFFSET यहाँ हमेशा अद्यतन नामित श्रेणी लौटाएगा।

फिर, आपको नाम प्रबंधक . पर ले जाया जाएगा जादूगर।
➤ बंद करें➤ दबाएं

3.2:डेटा सत्यापन सूची लागू करने के लिए VBA कोड का उपयोग करना

➤ शीट के नाम पर राइट-क्लिक करें और कोड देखें . चुनें विकल्प।

बाद में, कोड विंडो दिखाई देगी।

➤ निम्न कोड टाइप करें

Sub worksheet_Change(ByVal newitem As Range)

Dim updatedrange, item
If Not Intersect(newitem, Range("B:B")) Is Nothing Then
For Each item In Range("Vegetables")
updatedrange = updatedrange & "," & item
Next item
With ActiveSheet.Range("D6").Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
       xlBetween, Formula1:=updatedrange
      .IgnoreBlank = True
      .InCellDropdown = True
      .InputTitle = ""
      .ErrorTitle = ""
      .InputMessage = ""
      .ErrorMessage = ""
      .ShowInput = True
      .ShowError = True
End With
End If

End Sub

यह कोड तभी निष्पादित होगा जब मूल्यों या जोड़ में कोई परिवर्तन हुआ हो और इसलिए हमने इस प्रक्रिया को Worksheet_Change के रूप में परिभाषित किया है। , कार्यपत्रक वस्तु . है और बदलें प्रक्रिया . है .
नया आइटम उस सेल का पता होता है जिसमें हम नए मान जोड़ रहे हैं और हमने इसे रेंज के रूप में परिभाषित किया है . अपडेट की गई श्रेणी  . का डेटाटाइप और आइटम  संस्करण के रूप में माना जाएगा, जहां हमने अद्यतन श्रेणी . निर्दिष्ट किया है   सब्जियों और आइटम . की अद्यतन नामित श्रेणी में   इस श्रेणी के प्रत्येक सेल के मूल्यों के लिए है।
के लिए लूप अपडेट की गई श्रेणी को अपडेट की गई श्रेणी . को असाइन करेगा   और साथ कथन एक ही वस्तु की पुनरावृत्ति से बच जाएगा, और अंत में, हमने सत्यापन . जोड़ा है ।

अब, मुख्य शीट पर लौटने और उत्पाद जोड़ने के बाद प्रभाव की जांच करने का समय आ गया है सलाद .
जैसा कि हम देख सकते हैं, हमारी ड्रॉपडाउन सूची में अब यह नया आइटम है।

इस नए आइटम को चुनने के बाद हम इसे सेल D6 . में रख रहे हैं ।

और पढ़ें: किसी अन्य शीट से डेटा सत्यापन सूची का उपयोग कैसे करें (6 तरीके)

विधि-4 :सशर्त ड्रॉप डाउन सूची बनाने के लिए नामांकित श्रेणी का उपयोग करना

यहां, हम सेल E6 . में एक ड्रॉपडाउन सूची बनाएंगे जो सेल की स्थिति पर निर्भर होगा D6 और इस कारण से, हमारे पास निम्नलिखित दो नामित श्रेणियां हैं, जैसे फल1 और सब्जी1 ।

कदम :
➤ चरण-01 . का पालन करें की विधि-1 .
➤ निम्नलिखित कोड लिखें

Sub Datavalidation4()

If Range("D6") = "Fruits" Then
Range("E6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:="=fruit1"
Else
Range("E6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:="=vegetable1"
End If

End Sub

अगर-तब स्टेटमेंट जाँच करेगा कि क्या सेल में मान D6 . है है फल और इस मान के लिए, एक सूची के रूप में, हम नामित श्रेणी प्राप्त करेंगे फल1 सेल में सूची के रूप में E6 अन्यथा हमें नामित श्रेणी मिलेगी सब्जी1 सेल E6 . में सूची के रूप में ।

➤ F5➤ दबाएं , फिर वर्कशीट पर जाएं और सेल E6 . के ड्रॉपडाउन सिंबल पर क्लिक करें .
उसके बाद, आपको श्रेणी के लिए फलों की सूची फल . के रूप में मिल जाएगी सेल में D6 और सूची में से कोई एक आइटम चुनें जैसे ब्लैकबेरी ।

तो, हमें हमारी वांछित वस्तु मिल गई है ब्लैकबेरी सूची से।

➤ श्रेणी को सब्जियां . के रूप में बदलने के लिए हम देख सकते हैं कि कोड चलाने के बाद हमारे पास सब्जियों की सूची है।

ब्रोकोली . चुनने के बाद हमें यह आइटम सेल E6 . में मिल रहा है ।

संबंधित सामग्री: एक्सेल में डेटा सत्यापन फॉर्मूला में IF स्टेटमेंट का उपयोग कैसे करें (6 तरीके)

अभ्यास अनुभाग

स्वयं अभ्यास करने के लिए हमने एक अभ्यास . प्रदान किया है अभ्यास . नामक शीट में नीचे जैसा अनुभाग . कृपया इसे स्वयं करें।

निष्कर्ष

इस लेख में, हमने एक्सेल VBA में डेटा सत्यापन सूची के लिए नामित श्रेणी का उपयोग करने के तरीकों को कवर करने का प्रयास किया है। सरलता। आशा है कि आपको यह उपयोगी लगेगा। यदि आपके कोई सुझाव या प्रश्न हैं, तो बेझिझक उन्हें टिप्पणी अनुभाग में साझा करें।

संबंधित लेख

• रंग के साथ एक्सेल में डेटा सत्यापन का उपयोग कैसे करें (4 तरीके)
• एरे से डेटा सत्यापन सूची बनाने के लिए एक्सेल वीबीए
• Excel डेटा सत्यापन में कस्टम VLOOKUP फ़ॉर्मूला का उपयोग कैसे करें
• [फिक्स्ड] डेटा सत्यापन एक्सेल में कॉपी पेस्ट के लिए काम नहीं कर रहा है (समाधान के साथ)
• Excel में डेटा सत्यापन सूची से रिक्त स्थान कैसे निकालें (5 तरीके)