एक्सेल में VBA के साथ डेटा सत्यापन ड्रॉप डाउन सूची (7 अनुप्रयोग)

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

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

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

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

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

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

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

चरण:

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

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

• उसके बाद, प्रतिलिपि करें निम्नलिखित कोड और चिपकाएं इसे कोड विंडो में।

Sub CreateDropDownList()
Range("B5").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="Grapes, Orange, Guava, Mango, Apple"
End Sub

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

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

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

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

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

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

2. एक्सेल में वीबीए के साथ नामांकित श्रेणी द्वारा ड्रॉप डाउन सूची बनाना

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

इस अनुभाग में, आप सीखेंगे कि नामांकित श्रेणी का उपयोग करके किसी दी गई सूची से ड्रॉपडाउन सूची कैसे तैयार करें VBA . के साथ कोड।

चरण:

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

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

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

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

• पहले की तरह ही, Visual Basic Editor खोलें डेवलपर . की ओर से टैब और सम्मिलित करें एक मॉड्यूल कोड विंडो में।
• फिर, कोड विंडो में, कॉपी करें निम्नलिखित कोड और चिपकाएं  यह।

Sub GenerateDropDownList()
Range("B12").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=Fruits"
End Sub

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

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

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

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

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

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

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

चरण:

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

Sub ProduceDropDownList()
With Range("B12").Validation
 .Add xlValidateList, xlValidAlertStop, xlBetween, "=$B$5:$B$10"
 .InCellDropdown = True
End With
End Sub

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

कोड का यह भाग सेल B12 में ड्रॉपडाउन सूची produce तैयार करेगा B5:B9 श्रेणी में मौजूद मान . के साथ ।

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

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

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

समान रीडिंग:

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

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

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

चरण:

• सबसे पहले, विजुअल बेसिक संपादक खोलें डेवलपर . की ओर से टैब और सम्मिलित करें एक मॉड्यूल कोड विंडो में।
• दूसरा, कोड विंडो में, कॉपी करें निम्नलिखित कोड और चिपकाएं  यह।

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

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

यह कोड बी5 से बी9 तक के प्रत्येक सेल में एक ड्रॉपडाउन सूची तैयार करेगा ।

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

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

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

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

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

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

चरण:

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

• फिर, प्रतिलिपि करें निम्नलिखित कोड और चिपकाएं इसे ऑटो-जेनरेटेड कोड विंडो में डालें।

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

• इस कोड को न चलाएं। सहेजें  यह।

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

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

• फिर, दर्ज करें press दबाएं ।

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

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

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

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

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

आइए देखें कि 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

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

• Later, Run the macro and look at the following image to see the output.

As a result of the successful code execution, there is a dropdown list created in Cell B5 of the Target worksheet with the values “Grapes, Orange, Guava, Mango, Apple ” that are stored in range B5:B9 of the List spreadsheet.

और पढ़ें: How to Use Data Validation List from Another Sheet (6 Methods)

7. Deleting Data Validation Drop Down List from Excel with VBA Macro

This section will show you how to delete a dropdown list from Excel. We will show you how to delete the existing dropdown list in Cell B5 ( shown in the image below) with the VBA मैक्रो।

The steps to execute that are given below.

चरण:

• At first, open Visual Basic Editor डेवलपर . की ओर से टैब और सम्मिलित करें एक मॉड्यूल कोड विंडो में।
• Then, in the code window, copy the following code and paste

Sub DeleteDropDownList()
Range("B5").Validation.Delete
End Sub

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

• Later, Run the macro and look at the following image.

As you can see from the above image is that there is no dropdown list anymore in Cell B5 . Finally, we have learnt how to delete an existing dropdown list from a spreadsheet with VBA ।

और पढ़ें: How to Remove Blanks from Data Validation List in Excel (5 Methods)

निष्कर्ष

To conclude, this article showed you 7 different applications of the data validation drop-down list एक्सेल में VBA मैक्रो . के साथ . मुझे उम्मीद है कि यह लेख आपके लिए बहुत फायदेमंद रहा होगा। विषय के संबंध में कोई भी प्रश्न पूछने के लिए स्वतंत्र महसूस करें।

संबंधित लेख

• [Fixed] Data Validation Not Working for Copy Paste in Excel (with Solution)
• एक्सेल में वीबीए के साथ डेटा सत्यापन सूची के लिए नामांकित श्रेणी का उपयोग कैसे करें
• एरे से डेटा सत्यापन सूची बनाने के लिए एक्सेल वीबीए
• How to Use Data Validation in Excel with Color (4 Ways)
• Apply Multiple Data Validation in One Cell in Excel (3 Examples)