Computer >> कंप्यूटर ट्यूटोरियल >  >> सॉफ्टवेयर >> Office

एक्सेल VBA में महारत हासिल करें: नामित रेंज के साथ डायनामिक ड्रॉपडाउन बनाएं

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

वर्कबुक डाउनलोड करें

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

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

एक्सेल VBA में महारत हासिल करें: नामित रेंज के साथ डायनामिक ड्रॉपडाउन बनाएं

हमने यहां माइक्रोसॉफ्ट एक्सेल 365 संस्करण का उपयोग किया है, आप अपनी सुविधा के अनुसार किसी अन्य संस्करण का उपयोग कर सकते हैं।

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

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

एक्सेल VBA में महारत हासिल करें: नामित रेंज के साथ डायनामिक ड्रॉपडाउन बनाएं

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

एक्सेल VBA में महारत हासिल करें: नामित रेंज के साथ डायनामिक ड्रॉपडाउन बनाएं

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

एक्सेल VBA में महारत हासिल करें: नामित रेंज के साथ डायनामिक ड्रॉपडाउन बनाएं

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

एक्सेल VBA में महारत हासिल करें: नामित रेंज के साथ डायनामिक ड्रॉपडाउन बनाएं

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

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

यहां, Validation को सेल D6 में जोड़ा जाएगा, xlValidateList ड्रॉप-डाउन सूची बनाने के लिए है और सूत्र का उपयोग रेंज के नाम "=Fruits" के रूप में किया जाता है।

एक्सेल VBA में महारत हासिल करें: नामित रेंज के साथ डायनामिक ड्रॉपडाउन बनाएं

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

एक्सेल VBA में महारत हासिल करें: नामित रेंज के साथ डायनामिक ड्रॉपडाउन बनाएं

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

एक्सेल VBA में महारत हासिल करें: नामित रेंज के साथ डायनामिक ड्रॉपडाउन बनाएं

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

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

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

एक्सेल VBA में महारत हासिल करें: नामित रेंज के साथ डायनामिक ड्रॉपडाउन बनाएं

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

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

सबसे पहले, यह वर्कशीट Add की रेंज "B4:B10" में नाम Fruit जोड़ देगा।
फिर, हम सेल D6 में Validation जोड़ेंगे, xlValidateList ड्रॉप-डाउन सूची बनाने के लिए है और सूत्र का उपयोग रेंज के नाम "=Fruit" के रूप में किया जाता है।

एक्सेल VBA में महारत हासिल करें: नामित रेंज के साथ डायनामिक ड्रॉपडाउन बनाएं

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

एक्सेल VBA में महारत हासिल करें: नामित रेंज के साथ डायनामिक ड्रॉपडाउन बनाएं

तो, हमें सूची से हमारा वांछित आइटम Blueberries मिल गया है और इसके अलावा, हम फलों के लिए अपना बनाया गया नामित रेंज देख सकते हैं।

एक्सेल VBA में महारत हासिल करें: नामित रेंज के साथ डायनामिक ड्रॉपडाउन बनाएं

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

समान पठन:

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

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

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

एक्सेल VBA में महारत हासिल करें: नामित रेंज के साथ डायनामिक ड्रॉपडाउन बनाएं

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

एक्सेल VBA में महारत हासिल करें: नामित रेंज के साथ डायनामिक ड्रॉपडाउन बनाएं

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

3.1: अद्यतन नामित रेंज बनाना

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

एक्सेल VBA में महारत हासिल करें: नामित रेंज के साथ डायनामिक ड्रॉपडाउन बनाएं

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

एक्सेल VBA में महारत हासिल करें: नामित रेंज के साथ डायनामिक ड्रॉपडाउन बनाएं

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

=OFFSET(Update!$B$4, 0, 0, COUNTA(Update!$B:$B)-2)

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

एक्सेल VBA में महारत हासिल करें: नामित रेंज के साथ डायनामिक ड्रॉपडाउन बनाएं

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

एक्सेल VBA में महारत हासिल करें: नामित रेंज के साथ डायनामिक ड्रॉपडाउन बनाएं

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

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

एक्सेल VBA में महारत हासिल करें: नामित रेंज के साथ डायनामिक ड्रॉपडाउन बनाएं

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

एक्सेल 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 के रूप में परिभाषित किया है, Worksheet ऑब्जेक्ट है और Change प्रक्रिया है।
newitem उस सेल का पता रखता है जिसमें हम नए मान जोड़ रहे हैं और हमने इसे रेंज के रूप में परिभाषित किया है। updatedrange और item का डेटा प्रकार वैरिएंट माना जाएगा, जहां हमने updatedrange को सब्जियों की अद्यतन नामित रेंज और item को इस रेंज के प्रत्येक सेल के मान के लिए असाइन किया है।
FOR लूप updatedrange को अद्यतन रेंज असाइन करेगा और WITH स्टेटमेंट उसी ऑब्जेक्ट की पुनरावृत्ति से बचाएगा, और अंत में, हमने सत्यापन जोड़ा है।

एक्सेल VBA में महारत हासिल करें: नामित रेंज के साथ डायनामिक ड्रॉपडाउन बनाएं

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

एक्सेल VBA में महारत हासिल करें: नामित रेंज के साथ डायनामिक ड्रॉपडाउन बनाएं

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

एक्सेल VBA में महारत हासिल करें: नामित रेंज के साथ डायनामिक ड्रॉपडाउन बनाएं

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

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

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

एक्सेल VBA में महारत हासिल करें: नामित रेंज के साथ डायनामिक ड्रॉपडाउन बनाएं

एक्सेल VBA में महारत हासिल करें: नामित रेंज के साथ डायनामिक ड्रॉपडाउन बनाएं

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

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

IF-THEN स्टेटमेंट जांचेगा कि सेल D6 में मान Fruits है और इस मान के लिए, एक सूची के रूप में, हमें सेल E6 में सूची के रूप में नामित रेंज fruit1 मिलेगा अन्यथा हमें सेल E6 में सूची के रूप में नामित रेंज vegetable1 मिलेगा।

एक्सेल VBA में महारत हासिल करें: नामित रेंज के साथ डायनामिक ड्रॉपडाउन बनाएं

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

एक्सेल VBA में महारत हासिल करें: नामित रेंज के साथ डायनामिक ड्रॉपडाउन बनाएं

तो, हमें सूची से हमारा वांछित आइटम Blackberries मिल गया है।

एक्सेल VBA में महारत हासिल करें: नामित रेंज के साथ डायनामिक ड्रॉपडाउन बनाएं

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

एक्सेल VBA में महारत हासिल करें: नामित रेंज के साथ डायनामिक ड्रॉपडाउन बनाएं

Broccoli चुनने के बाद हम इसे सेल E6 में प्राप्त कर रहे हैं।

एक्सेल VBA में महारत हासिल करें: नामित रेंज के साथ डायनामिक ड्रॉपडाउन बनाएं

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

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

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

एक्सेल VBA में महारत हासिल करें: नामित रेंज के साथ डायनामिक ड्रॉपडाउन बनाएं

निष्कर्ष

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

संबंधित लेख

  • रंग के साथ एक्सेल में डेटा सत्यापन का उपयोग कैसे करें (4 तरीके)
  • एक्सेल VBA से सरणी से डेटा सत्यापन सूची बनाएं
  • एक्सेल डेटा सत्यापन में कस्टम VLOOKUP सूत्र का उपयोग कैसे करें
  • [फिक्स्ड] कॉपी पेस्ट के लिए एक्सेल में डेटा सत्यापन काम नहीं कर रहा (समाधान के साथ)
  • एक्सेल में डेटा सत्यापन सूची से रिक्त कैसे हटाएं (5 तरीके)
  1. Excel VBA में डेटा एंट्री फॉर्म बनाएं – चरण-दर-चरण पूरी गाइड Excel VBA में डेटा एंट्री फॉर्म बनाएं – चरण-दर-चरण पूरी गाइड

    Excel में VBA के साथ काम करते समय, हम में से ज्यादातर लोगों को डेटा एंट्री फॉर्म बनाने में समस्या आती है। इस लेख में, मैं आपको दिखाऊंगा कि कैसे आप VBA का उपयोग करके Excel में बहुत आसानी और पेशेवर तरीके से डेटा एंट्री फॉर्म बना सकते हैं। Excel VBA में डेटा एंट्री फॉर्म बनाने के 3 सरल चरण यहां मेरे

  1. एक्सेल सॉल्वर के साथ रैखिक प्रोग्रामिंग में महारत: चरण-दर-चरण गाइड एक्सेल सॉल्वर के साथ रैखिक प्रोग्रामिंग में महारत: चरण-दर-चरण गाइड

    हम एक्सेल का उपयोग कई अलग-अलग गणितीय संक्रियाओं को करने के लिए कर सकते हैं। रैखिक प्रोग्रामिंग सांख्यिकी और अनुप्रयुक्त गणित का एक पहलू है। इसके विशाल व्यावहारिक अनुप्रयोग हैं। रैखिक प्रोग्रामिंग समस्याओं को मैन्युअल रूप से हल करना परेशानी भरा लग सकता है। दूसरी ओर, हमारे पास एक्सेल सॉल्वर है जो उन स

  1. एक्सेल में टेक्स्ट-टू-कॉलम का उपयोग करके तारीखों को संभालना: एक चरण-दर-चरण मार्गदर्शिका एक्सेल में टेक्स्ट-टू-कॉलम का उपयोग करके तारीखों को संभालना: एक चरण-दर-चरण मार्गदर्शिका

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