VBA का उपयोग करके एक्सेल ड्रॉप-डाउन सूची से मान चुनने के 2 सिद्ध तरीके

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

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

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

एक्सेल में सामान्य सूची से ड्रॉपडाउन सूची बनाना

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

निम्नलिखित सामान्य सूची हमारे एक्सेल वर्कशीट में है। सूची में दोहराव वाले मान हैं (उदाहरण के लिए, सेल B7 और B9 में सेब)।

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

चरण:

• सबसे पहले, किसी भी सेल पर क्लिक करें (हमारे मामले में सेल D4) जहां आप ड्रॉपडाउन सूची स्टोर करना चाहते हैं।
• फिर, डेटा टैब पर क्लिक करें।
• उसके बाद, रिबन के डेटा टूल्स समूह से डेटा वैलिडेशन चुनें।

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

• बाद में, ओके पर क्लिक करें।

निम्न छवि देखें।

सेल D4 में, एक बनाई गई ड्रॉपडाउन सूची है जो मानों को रखती है (उदाहरण के लिए, अंगूर, संतरा, सेब, आम, सेब) सामान्य सूची से पुनर्प्राप्त (रेंज B5:B9)।

एक्सेल में ड्रॉप डाउन सूची से मान चुनने के लिए VBA के साथ 2 विधियां

इस खंड में, आप सीखेंगे कि VBA के साथ एक्सेल में दोहराव और गैर-दोहराव वाले मानों दोनों के साथ ड्रॉपडाउन सूची से कई मान कैसे चुनें।

1. एक्सेल में ड्रॉप डाउन सूची से कई मान चुनने के लिए VBA एम्बेड करें (दोहराव वाले मानों के साथ)

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

चरण:

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

• अगला, उपयुक्त शीट नाम पर राइट-क्लिक करें और दिखाई देने वाली विकल्प सूची से व्यू कोड चुनें।

• फिर, निम्नलिखित कोड कॉपी करें और कोड विंडो में पेस्ट करें।

Private Sub Worksheet_Change(ByVal Target As Range) Dim ValueA As String Dim ValueB As String On Error GoTo Exitsub If Target.Address = "$D$4" Then If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then GoTo Exitsub Else: If Target.Value = "" Then GoTo Exitsub Else Application.EnableEvents = False ValueB = Target.Value Application.Undo ValueA = Target.Value If ValueA = "" Then Target.Value = ValueB Else Target.Value = ValueA & ", " & ValueB End If End If End If Application.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub

• यह कोड न चलाएं, सेव करें।
• अब रुचि की वर्कशीट पर वापस जाएं. यदि आप सेल D4 में बनाई गई ड्रॉपडाउन सूची पर क्लिक करते हैं, तो आप देखेंगे कि अब आप ड्रॉपडाउन से कई मान चुन सकते हैं (निम्नलिखित gif देखें)।

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

VBA कोड व्याख्या

Dim ValueA As String Dim ValueB As String

चर नाम परिभाषित करना।

On Error GoTo Exitsub

यदि कोई त्रुटि होती है, तो लेबल Exitsub पर जाएं।

If Target.Address = "$D$4" Then If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then GoTo Exitsub

गंतव्य को सेल D4 के रूप में सेट करता है जिसमें डेटा वैलिडेशन है। यदि कोई सेल नहीं हैं जिनमें डेटा वैलिडेशन है, तो लेबल Exitsub पर जाएं।

Else: If Target.Value = "" Then GoTo Exitsub Else

यदि गंतव्य के रूप में कोई सेल नहीं हैं, तो लेबल Exitsub पर जाएं। अन्यथा, निम्नलिखित पंक्तियों को निष्पादित करें।

Application.EnableEvents = False

एप्लिकेशन इवेंट्स को बंद करना ताकि Worksheet_Change मैक्रो को फायर होने से रोका जा सके; अन्यथा, यह संभावित अनंत लूप का कारण बन सकता है।

ValueB = Target.Value

ValueB को बदले गए सेल के नए मान के रूप में परिभाषित करना।

Application.Undo

बदले गए सेल को पूर्ववत करना।

ValueA = Target.Value

परिवर्तन को पूर्ववत करके, हम अब ValueA को बदले गए सेल के पुराने मान के रूप में परिभाषित कर सकते हैं।

If ValueA = "" Then Target.Value = ValueB

यदि पुराना मान खाली है, तो नए मान को गंतव्य के रूप में स्टोर करें।

Else Target.Value = ValueA & ", " & ValueB End If End If End If

अन्यथा, पुराने और नए दोनों मानों को अल्पविराम (,) के साथ जोड़कर गंतव्य मान के रूप में सेट करें। सभी If स्टेटमेंट बंद करना।

Application.EnableEvents = True Exitsub: Application.EnableEvents = True

एप्लिकेशन इवेंट्स को वापस चालू करना।

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

समान पठन:

• एक्सेल में किसी अन्य शीट से ड्रॉप डाउन सूची कैसे बनाएं (2 विधियां)
• एक्सेल में सर्चेबल ड्रॉप डाउन सूची बनाएं (2 विधियां)
• एक्सेल ड्रॉप डाउन सूची काम नहीं कर रही (8 मुद्दे और समाधान)
• टेबल से एक्सेल ड्रॉप डाउन सूची बनाएं (5 उदाहरण)
• एक्सेल में ऑटो अपडेट ड्रॉप डाउन सूची (3 तरीके)

2. ड्रॉप डाउन सूची से कई मान चुनने के लिए VBA मैक्रो लागू करें (बिना दोहराव वाले मानों के)

हमारे डेटासेट में दोहराव वाले मान हैं। यदि आप चाहते हैं कि आपकी ड्रॉपडाउन सूची दोहराव वाले मानों को छोड़कर सभी मानों को पकड़े, तो नीचे दिए गए चरणों का पालन करें।

चरण:

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

Private Sub Worksheet_Change(ByVal Target As Range) Dim ValueA As String Dim ValueB As String Application.EnableEvents = True On Error GoTo Exitsub If Target.Address = "$D$4" Then If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then GoTo Exitsub Else: If Target.Value = "" Then GoTo Exitsub Else Application.EnableEvents = False ValueB = Target.Value Application.Undo ValueA = Target.Value If ValueA = "" Then Target.Value = ValueB Else If InStr(1, ValueA, ValueB) = 0 Then Target.Value = ValueA & ", " & ValueB Else: Target.Value = ValueA End If End If End If End If Application.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub

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

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

VBA कोड व्याख्या

Dim ValueA As String Dim ValueB As String

चर नाम परिभाषित करना।

Application.EnableEvents = True

एप्लिकेशन इवेंट्स को वापस चालू करना।

On Error GoTo Exitsub

यदि कोई त्रुटि होती है, तो लेबल Exitsub पर जाएं।

If Target.Address = "$D$4" Then If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then GoTo Exitsub

गंतव्य को सेल D4 के रूप में सेट करता है जिसमें डेटा वैलिडेशन है। यदि कोई सेल नहीं हैं जिनमें डेटा वैलिडेशन है, तो लेबल Exitsub पर जाएं।

Else: If Target.Value = "" Then GoTo Exitsub Else

यदि गंतव्य के रूप में कोई सेल नहीं हैं, तो लेबल Exitsub पर जाएं। अन्यथा, निम्नलिखित पंक्तियों को निष्पादित करें।

Application.EnableEvents = False

एप्लिकेशन इवेंट्स को बंद करना ताकि Worksheet_Change मैक्रो को फायर होने से रोका जा सके जो संभावित अनंत लूप का कारण बन सकता है।

ValueB = Target.Value

ValueB को बदले गए सेल के नए मान के रूप में परिभाषित करना।

Application.Undo

बदले गए सेल को पूर्ववत करना।

ValueA = Target.Value

परिवर्तन को पूर्ववत करके, हम अब ValueA को बदले गए सेल के पुराने मान के रूप में परिभाषित कर सकते हैं।

If ValueA = "" Then Target.Value = ValueB

यदि पुराना मान खाली है, तो नए मान को गंतव्य के रूप में स्टोर करें।

Else If InStr(1, ValueA, ValueB) = 0 Then Target.Value = ValueA & ", " & ValueB

InStr फंक्शन एक स्ट्रिंग में सबस्ट्रिंग की पहली घटना की स्थिति लौटाता है। यदि आउटपुट 0 है तो पुराने और नए दोनों मानों को अल्पविराम (,) के साथ जोड़कर गंतव्य मान के रूप में सेट करें।

Else: Target.Value = ValueA End If End If End If End If

अन्यथा, पुराने मान को गंतव्य के रूप में सेट करें। सभी If स्टेटमेंट बंद करना।

Application.EnableEvents = True Exitsub: Application.EnableEvents = True

एप्लिकेशन इवेंट्स को वापस चालू करना।

और पढ़ें: चयन के आधार पर एक्सेल ड्रॉप डाउन सूची

निष्कर्ष

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

संबंधित लेख

• फॉर्मूला के आधार पर एक्सेल में ड्रॉप-डाउन सूची कैसे बनाएं (4 तरीके)
• एक्सेल में सशर्त ड्रॉप डाउन सूची (बनाएं, सॉर्ट करें और उपयोग करें)
• एक्सेल में ड्रॉप-डाउन सूची बनाने के लिए IF स्टेटमेंट का उपयोग कैसे करें
• सेल मान को ड्रॉप डाउन सूची से लिंक करें एक्सेल में (5 तरीके)
• एक्सेल में ड्रॉप-डाउन सूची कैसे हटाएं