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

एक्सेल मास्टरी के लिए 7 VBA-एन्हांस्ड डेटा सत्यापन ड्रॉप-डाउन सूचियां

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

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

आप यहां से मुफ्त अभ्यास एक्सेल वर्कबुक डाउनलोड कर सकते हैं।

VBA के साथ एक्सेल में डेटा सत्यापन ड्रॉप-डाउन सूची के 7 तरीके

इस खंड में, आप VBA मैक्रो के साथ एक्सेल में डेटा सत्यापन ड्रॉप-डाउन सूची के 7 अलग-अलग अनुप्रयोग सीखेंगे।

1. एक्सेल में डेटा सत्यापन ड्रॉप-डाउन सूची बनाने के लिए VBA एम्बेड करें

यह जानने के लिए कि VBA के साथ डेटा सत्यापन ड्रॉप-डाउन सूची कैसे बनाएं, नीचे दिए गए चरणों का पालन करें।

चरण:

  • शुरुआत में, अपने कीबोर्ड पर Alt + F11 दबाएं या डेवलपर -> विजुअल बेसिक टैब पर जाकर विजुअल बेसिक एडिटर खोलें।

एक्सेल मास्टरी के लिए 7 VBA-एन्हांस्ड डेटा सत्यापन ड्रॉप-डाउन सूचियां

  • फिर, पॉप-अप कोड विंडो में, मेनू बार से, इंसर्ट -> मॉड्यूल पर क्लिक करें।

एक्सेल मास्टरी के लिए 7 VBA-एन्हांस्ड डेटा सत्यापन ड्रॉप-डाउन सूचियां

  • उसके बाद, निम्न कोड को कॉपी करें और कोड विंडो में पेस्ट करें।
Sub CreateDropDownList() Range("B5").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="Grapes, Orange, Guava, Mango, Apple" End Sub

आपका कोड अब चलाने के लिए तैयार है।

एक्सेल मास्टरी के लिए 7 VBA-एन्हांस्ड डेटा सत्यापन ड्रॉप-डाउन सूचियां

यह कोड सेल B5 में एक ड्रॉप-डाउन सूची बनाएगा। और ड्रॉप-डाउन सूची में मान "अंगूर, संतरा, अमरूद, आम, सेब" होंगे।

  • अब, अपने कीबोर्ड पर F5 दबाएं या मेनू बार से रन -> रन सब/यूजरफॉर्म चुनें। आप सब-मेनू बार में छोटे रन आइकन पर भी क्लिक कर सकते हैं मैक्रो चलाने के लिए।

एक्सेल मास्टरी के लिए 7 VBA-एन्हांस्ड डेटा सत्यापन ड्रॉप-डाउन सूचियां

कोड निष्पादन के बाद, परिणाम देखने के लिए नीचे दी गई छवि को देखें।

एक्सेल मास्टरी के लिए 7 VBA-एन्हांस्ड डेटा सत्यापन ड्रॉप-डाउन सूचियां

जैसा कि हम ऊपर की छवि से देख सकते हैं कि सेल B5 में मान "अंगूर, संतरा, अमरूद, आम, सेब" के साथ एक ड्रॉप-डाउन सूची बनाई गई है।

अधिक पढ़ें: डेटा सत्यापन के लिए एक्सेल ड्रॉप-डाउन सूची कैसे बनाएं (8 तरीके)

2. एक्सेल में VBA के साथ नामित रेंज द्वारा ड्रॉप-डाउन सूची जनरेट करना

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

इस खंड में, आप सीखेंगे कि नामित रेंज का उपयोग करके दिए गए सूची से ड्रॉप-डाउन सूची कैसे जनरेट करें VBA कोड के साथ।

चरण:

  • सबसे पहले, वह रेंज चुनें जहां ड्रॉप-डाउन सूची के मान मौजूद हैं (हमारे मामले में, रेंज B5:B9 है)।
  • दूसरा, चयनित रेंज पर राइट-क्लिक करें।
  • विकल्पों की एक सूची दिखाई देगी। वहां से, नाम परिभाषित करें… चुनें।

एक्सेल मास्टरी के लिए 7 VBA-एन्हांस्ड डेटा सत्यापन ड्रॉप-डाउन सूचियां

  • उसके बाद, एक नया नाम पॉप-अप बॉक्स दिखाई देगा। नाम बॉक्स में, कोई भी नाम लिखें जो आपको पसंद हो (हमने अपने सेल का नाम फल रखा)।
  • बाद में, OK पर क्लिक करें।

एक्सेल मास्टरी के लिए 7 VBA-एन्हांस्ड डेटा सत्यापन ड्रॉप-डाउन सूचियां

  • हमने सफलतापूर्वक रेंज B5:B9 को फल नाम दिया है (नीचे दी गई तस्वीर में दिखाया गया है)।

एक्सेल मास्टरी के लिए 7 VBA-एन्हांस्ड डेटा सत्यापन ड्रॉप-डाउन सूचियां

अब हम इस परिभाषित नाम को अपने VBA कोड में उपयोग करेंगे. ऐसा करने के चरण नीचे दिखाए गए हैं।

  • पहले की तरह ही, डेवलपर टैब से विजुअल बेसिक एडिटर खोलें और कोड विंडो में एक मॉड्यूल इंसर्ट करें।
  • फिर, कोड विंडो में, निम्न कोड को कॉपी करें और पेस्ट करें।
Sub GenerateDropDownList() Range("B12").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=Fruits" End Sub

आपका कोड अब चलाने के लिए तैयार है।

एक्सेल मास्टरी के लिए 7 VBA-एन्हांस्ड डेटा सत्यापन ड्रॉप-डाउन सूचियां

यह कोड सेल B12 में नाम फल में परिभाषित मानों "अंगूर, संतरा, अमरूद, आम, सेब" के साथ एक ड्रॉप-डाउन सूची बनाएगा।

  • बाद में, रन मैक्रो पर क्लिक करें। परिणाम नीचे दी गई छवि में दिखाया गया है।

एक्सेल मास्टरी के लिए 7 VBA-एन्हांस्ड डेटा सत्यापन ड्रॉप-डाउन सूचियां

परिणामस्वरूप, हम ऊपर की छवि से देख सकते हैं कि सेल B12 में मान "अंगूर, संतरा, अमरूद, आम, सेब" के साथ एक ड्रॉप-डाउन सूची बनाई गई है।

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

3. मैक्रो के साथ दी गई सूची से डेटा सत्यापन ड्रॉप-डाउन बॉक्स बनाना

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

चरण:

  • पहले की तरह, डेवलपर टैब से विजुअल बेसिक एडिटर खोलें और कोड विंडो में एक मॉड्यूल इंसर्ट करें।
  • फिर, निम्न कोड को कॉपी करें और कोड विंडो में पेस्ट करें।
Sub ProduceDropDownList() With Range("B12").Validation .Add xlValidateList, xlValidAlertStop, xlBetween, "=$B$5:$B$10" .InCellDropdown = True End With End Sub

आपका कोड अब चलाने के लिए तैयार है।

एक्सेल मास्टरी के लिए 7 VBA-एन्हांस्ड डेटा सत्यापन ड्रॉप-डाउन सूचियां

यह कोड सेल B12 में रेंज B5:B9 में मौजूद मानों के साथ एक ड्रॉप-डाउन सूची बनाएगा।

  • अब, रन मैक्रो पर क्लिक करें और आउटपुट देखने के लिए निम्न छवि को देखें।

एक्सेल मास्टरी के लिए 7 VBA-एन्हांस्ड डेटा सत्यापन ड्रॉप-डाउन सूचियां

परिणामस्वरूप, हम ऊपर की छवि से देख सकते हैं कि सेल B12 में मान "अंगूर, संतरा, अमरूद, आम, सेब" के साथ एक ड्रॉप-डाउन सूची बनाई गई है जो हमने वर्कशीट में सेल B5 से B9 में संग्रहीत की थी।

संबंधित सामग्री: एक्सेल VBA के साथ डेटा सत्यापन सूची में डिफ़ॉल्ट मान (मैक्रो और UserForm)

समान पठन:

  • फिल्टर के साथ एक्सेल डेटा सत्यापन ड्रॉप-डाउन सूची (2 तरीके)
  • डेटा सत्यापन केवल अल्फान्यूमेरिक (कस्टम सूत्र का उपयोग करके)
  • अन्य सेल मान के आधार पर एक्सेल डेटा सत्यापन
  • एक्सेल डेटा सत्यापन में कस्टम VLOOKUP सूत्र का उपयोग कैसे करें

4. एक्सेल में एकाधिक ड्रॉप-डाउन सूची बनाने के लिए VBA लागू करना

आप VBA मैक्रो के साथ एकाधिक ड्रॉप-डाउन सूची बना सकते हैं। आइए देखें कि एक्सेल में यह कैसे करें।

चरण:

  • सबसे पहले, डेवलपर टैब से विजुअल बेसिक एडिटर खोलें और कोड विंडो में एक मॉड्यूल इंसर्ट करें।
  • दूसरा, कोड विंडो में, निम्न कोड को कॉपी करें और पेस्ट करें।
Sub MultipleDropDownList(iTarget As Range, iSource As Range) 'to delete and add validation in the target range With iTarget.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=" & iSource.Address .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End Sub Sub DropDownRange() MultipleDropDownList Sheet7.Range("B5:B10"), Sheet7.Range("A1:A3") End Sub

आपका कोड अब चलाने के लिए तैयार है।

एक्सेल मास्टरी के लिए 7 VBA-एन्हांस्ड डेटा सत्यापन ड्रॉप-डाउन सूचियां

यह कोड रेंज B5 से B9 तक हर सेल में ड्रॉप-डाउन सूची जनरेट करेगा।

  • तीसरा, रन मैक्रो पर क्लिक करें। परिणाम देखने के लिए नीचे दिए गए GIF को देखें।

एक्सेल मास्टरी के लिए 7 VBA-एन्हांस्ड डेटा सत्यापन ड्रॉप-डाउन सूचियां

रेंज B5 से B9 तक प्रत्येक सेल अब एक ड्रॉप-डाउन सूची रख रहा है।

अधिक पढ़ें: एक्सेल में एकाधिक मानदंड के लिए कस्टम डेटा सत्यापन लागू करें (4 उदाहरण)

5. उपयोगकर्ता-परिभाषित फंक्शन के साथ ड्रॉप-डाउन सूची बनाने के लिए VBA लागू करना

आप एक्सेल में उपयोगकर्ता-परिभाषित फंक्शन (UDF) के साथ ड्रॉप-डाउन सूची भी बना सकते हैं।

उसे प्राप्त करने के चरण नीचे दिखाए गए हैं।

चरण:

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

एक्सेल मास्टरी के लिए 7 VBA-एन्हांस्ड डेटा सत्यापन ड्रॉप-डाउन सूचियां

  • फिर, निम्न कोड को कॉपी करें और ऑटो-जेनरेटेड कोड विंडो में पेस्ट करें।
Public Function DropDownUDF(iSource As Range) As Variant 'to delete and add validation in the specified range With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=" & iSource.Address .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With 'this will return the first value 'this reset the values when formula in sheet are refreshed DropDownUDF = VBA.Val(iSource(1)) End Function
  • इस कोड को न चलाएं। इसे सेव करें।

एक्सेल मास्टरी के लिए 7 VBA-एन्हांस्ड डेटा सत्यापन ड्रॉप-डाउन सूचियां

  • फिर, रुचि की वर्कशीट पर वापस जाएं।
  • उसके बाद, कोई भी सेल चुनें जहां आप ड्रॉप-डाउन सूची बनाना चाहते हैं (हमारे मामले में, यह सेल B11 है)।
  • उस सेल में, नवनिर्मित फंक्शन - DropDownUDF - लिखें जैसे आप कोई अन्य फंक्शन लिखते हैं। अर्थात, पहले बराबर चिह्न (=) रखें, फिर फंक्शन नाम लिखें, DropDownUDF और सेल संदर्भ पास करें (B5:B9) पैरेंथेसिस के अंदर।

सेल B11 में सूत्र इस तरह होगा:

=DropDownUDF(B5:B9)

एक्सेल मास्टरी के लिए 7 VBA-एन्हांस्ड डेटा सत्यापन ड्रॉप-डाउन सूचियां

  • फिर, Enter दबाएं।

एक्सेल मास्टरी के लिए 7 VBA-एन्हांस्ड डेटा सत्यापन ड्रॉप-डाउन सूचियां

परिणामस्वरूप, आपको सेल B11 में UDF द्वारा बनाई गई ड्रॉप-डाउन सूची मिलेगी जिसमें मान "अंगूर, संतरा, अमरूद, आम, सेब" होंगे जो रेंज B5:B9 में संग्रहीत हैं और जिन्हें हमने फंक्शन के अंदर पास किया था।

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

6. VBA के साथ ड्रॉप-डाउन सूची में अलग शीट से डेटा निकालना

निम्न छवि को देखें। हमारे पास सूची नामक शीट में एक डेटासेट है।

एक्सेल मास्टरी के लिए 7 VBA-एन्हांस्ड डेटा सत्यापन ड्रॉप-डाउन सूचियां

हम यहां जो करने जा रहे हैं, वह है लक्ष्य नामक शीट के सेल B5 में एक ड्रॉप-डाउन सूची बनाना (नीचे दी गई तस्वीर में दिखाया गया है)। और उस ड्रॉप-डाउन सूची के मान सूची शीट के रेंज B5:B9 के मान होंगे।

एक्सेल मास्टरी के लिए 7 VBA-एन्हांस्ड डेटा सत्यापन ड्रॉप-डाउन सूचियां

आइए देखें कि VBA के साथ यह कैसे करें।

चरण:

  • सबसे पहले, डेवलपर टैब से विजुअल बेसिक एडिटर खोलें और कोड विंडो में एक मॉड्यूल इंसर्ट करें।
  • फिर, निम्न कोड को कॉपी करें और कोड विंडो में पेस्ट करें।
Private Sub DropDownFromSheet() 'to store the dropdown list in cell B5 'you can replace "B5" with any other cell With Range("B5").Validation .Delete 'to extract data from "List" sheet and "B5:B9" range 'you can replace "=List!B5:B9" with your sheet name and range .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="=List!B5:B9" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End Sub

आपका कोड अब चलाने के लिए तैयार है।

एक्सेल मास्टरी के लिए 7 VBA-एन्हांस्ड डेटा सत्यापन ड्रॉप-डाउन सूचियां

  • बाद में, रन मैक्रो पर क्लिक करें और आउटपुट देखने के लिए निम्न छवि को देखें।

एक्सेल मास्टरी के लिए 7 VBA-एन्हांस्ड डेटा सत्यापन ड्रॉप-डाउन सूचियां

सफल कोड निष्पादन के परिणामस्वरूप, लक्ष्य वर्कशीट के सेल B5 में एक ड्रॉप-डाउन सूची बनाई गई है जिसमें मान "अंगूर, संतरा, अमरूद, आम, सेब" हैं जो सूची स्प्रेडशीट के रेंज B5:B9 में संग्रहीत हैं।

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

7. VBA मैक्रो के साथ एक्सेल से डेटा सत्यापन ड्रॉप-डाउन सूची हटाना

यह खंड आपको दिखाएगा कि एक्सेल से ड्रॉप-डाउन सूची कैसे हटाएं। हम आपको दिखाएंगे कि VBA मैक्रो के साथ सेल B5 में मौजूदा ड्रॉप-डाउन सूची को कैसे हटाएं (नीचे दी गई छवि में दिखाया गया है)।

एक्सेल मास्टरी के लिए 7 VBA-एन्हांस्ड डेटा सत्यापन ड्रॉप-डाउन सूचियां

उसे निष्पादित करने के चरण नीचे दिए गए हैं।

चरण:

  • सबसे पहले, डेवलपर टैब से विजुअल बेसिक एडिटर खोलें और कोड विंडो में एक मॉड्यूल इंसर्ट करें।
  • फिर, कोड विंडो में, निम्न कोड को कॉपी करें और पेस्ट करें
Sub DeleteDropDownList() Range("B5").Validation.Delete End Sub

आपका कोड अब चलाने के लिए तैयार है।

  • बाद में, रन मैक्रो पर क्लिक करें और निम्न छवि को देखें।

एक्सेल मास्टरी के लिए 7 VBA-एन्हांस्ड डेटा सत्यापन ड्रॉप-डाउन सूचियां

जैसा कि आप ऊपर की छवि से देख सकते हैं कि सेल B5 में अब कोई ड्रॉप-डाउन सूची नहीं है। अंत में, हमने सीखा कि VBA के साथ स्प्रेडशीट से मौजूदा ड्रॉप-डाउन सूची कैसे हटाएं।

अधिक पढ़ें: एक्सेल में डेटा सत्यापन सूची से रिक्त स्थान कैसे हटाएं (5 तरीके)

निष्कर्ष

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

संबंधित लेख

  • [फिक्स्ड] कॉपी पेस्ट के लिए एक्सेल में डेटा सत्यापन काम नहीं कर रहा (समाधान के साथ)
  • VBA के साथ एक्सेल में डेटा सत्यापन सूची के लिए नामित रेंज का उपयोग कैसे करें
  • एक्सेल VBA से ऐरे से डेटा सत्यापन सूची बनाएं
  • रंग के साथ एक्सेल में डेटा सत्यापन का उपयोग कैसे करें (4 तरीके)
  • एक्सेल में एक सेल में एकाधिक डेटा सत्यापन लागू करें (3 उदाहरण)

  1. एक्सेल में मल्टी-वर्ड डिपेंडेंट ड्रॉप-डाउन सूचियाँ बनाएँ – चरण-दर-चरण गाइड एक्सेल में मल्टी-वर्ड डिपेंडेंट ड्रॉप-डाउन सूचियाँ बनाएँ – चरण-दर-चरण गाइड

    Microsoft Excel में काम करते समय, कभी-कभी हमें डेटा एंट्री फॉर्म या Excel डैशबोर्ड बनाने की आवश्यकता होती है। डेटा एंट्री फॉर्म विकसित करते समय, ड्रॉप-डाउन सूची Excel में एक बहुत उपयोगी सुविधा है। यह एक सेल में विकल्पों की सूची को ड्रॉप-डाउन मेनू के रूप में प्रदर्शित करती है, जिससे उपयोगकर्ता चुनाव

  1. ड्रॉप-डाउन सूची के साथ एक्सेल डेटा एंट्री फॉर्म बनाएं – दो आसान तरीके ड्रॉप-डाउन सूची के साथ एक्सेल डेटा एंट्री फॉर्म बनाएं – दो आसान तरीके

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

  1. एक्सेल डेटा वैलिडेशन ग्रे आउट क्यों है और इसे कैसे ठीक करें: 4 सिद्ध समाधान एक्सेल डेटा वैलिडेशन ग्रे आउट क्यों है और इसे कैसे ठीक करें: 4 सिद्ध समाधान

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