এক্সেল VBA মাস্টার করুন: নেমড রেঞ্জ দিয়ে ডাইনামিক ড্রপ-ডাউন তৈরি করুন

আপনি যদি এক্সেল VBA-তে ডেটা ভ্যালিডেশন লিস্টের জন্য নেমড রেঞ্জ ব্যবহারের সহজতম উপায় খুঁজছেন, তবে এই আর্টিকেলটি আপনার জন্য উপকারী হবে। নেমড রেঞ্জ ডেটা ভ্যালিডেশন ফর্মুলায় ব্যবহার করা একটি ড্রপ-ডাউন লিস্ট সহজে 만드는 জন্য উপকারী এবং এই কাজটি কিছু VBA কোডের সাহায্যে অত্যন্ত সহজ করা যায়।
তাই, চলুন আমাদের মূল আর্টিকেল শুরু করি নেমড রেঞ্জ ডেটা ভ্যালিডেশন লিস্টে ব্যবহারের উপায়গুলো এক্সপ্লোর করতে।

ওয়ার্কবুক ডাউনলোড করুন

VBA-তে নেমড রেঞ্জ ব্যবহার করে ডেটা ভ্যালিডেশন লিস্ট তৈরির ৪ উপায়

এখানে, আমাদের কাছে নিচের ডেটাসেট আছে যেটিতে কিছু প্রোডাক্ট এবং তাদের রেসপেক্টিভ সেলসপারসনদের লিস্টের রেকর্ড আছে। এই ডেটাসেট ব্যবহার করে আমরা ভিন্ন ভিন্ন VBA কোডের মাধ্যমে নেমড রেঞ্জ ডেটা ভ্যালিডেশন লিস্টে ব্যবহারের বিভিন্ন উপায় দেখাব।

এখানে আমরা Microsoft Excel 365 ভার্সন ব্যবহার করেছি, আপনি আপনার সুবিধা অনুযায়ী অন্য ভার্সনও ব্যবহার করতে পারেন।

পদ্ধতি-১: ড্রপ-ডাউন লিস্ট बनाने کے لیے نيمड رینج کو ڈیٹا ولیڈیشن میں استعمال کریں

এখানে, আমরা Fruits কলামের রেঞ্জকে Fruits নামে নামকরণ করেছি এবং একটি VBA কোড ব্যবহার করে সেল D6-এ একটি ড্রপ-ডাউন লিস্ট তৈরি করব।

স্টেপ-০১:
➤ Developer ট্যাব >> Visual Basic অপশনে যান।

তারপর, Visual Basic Editor খুলবে।
➤ Insert ট্যাব >> Module অপশনে যান।

এরপর, একটি Module তৈরি হবে।

স্টেপ-০২:
➤ নিচের কোডটি লিখুন

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

এখানে, Validation সেল D6-এ যোগ হবে, xlValidateList ড্রপ-ডাউন লিস্ট তৈরির জন্য এবং ফর্মুলাটি রেঞ্জের নাম হিসেবে ব্যবহার করা হয়েছে “=Fruits”।

➤ F5 চাপুন এবং পরে সেল D6-এর ড্রপ-ডাউন সিম্বলে ক্লিক করুন।
তারপর, আপনি ফলের লিস্ট পাবেন এবং লিস্ট থেকে যেকোনো একটি আইটেম সিলেক্ট করুন যেন Cherries।

শেষে, আমরা সেল D6-এ আমাদের সিলেক্ট করা আইটেম পাচ্ছি।

আরও পড়ুন: এক্সেলে টেবিল থেকে ডেটা ভ্যালিডেশন লিস্ট তৈরি করা (৩ পদ্ধতি)

পদ্ধতি-২: VBA কোড দিয়ে নেমড রেঞ্জ এবং ডেটা ভ্যালিডেশন লিস্ট যোগ করুন

এখানে আমরা ম্যানুয়ালি কোনো নেমড রেঞ্জ তৈরি করব না, বরং একটি সিম্পল VBA কোড একটি নেমড রেঞ্জ তৈরি করবে, এবং তারপর সেটি ব্যবহার করে আমরা সেল D6-এ ড্রপ-ডাউন লিস্ট পাব।

ধাপসমূহ:
➤ পদ্ধতি-১-এর স্টেপ-০১ অনুসরণ করুন।
➤ নিচের কোডটি লিখুন

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

প্রথমে, এটি Fruit নামে Add ওয়ার্কশিটের “B4:B10” রেঞ্জকে নাম দেবে।
তারপর, আমরা সেল D6-এ Validation যোগ করব, xlValidateList ড্রপ-ডাউন লিস্ট তৈরির জন্য এবং ফর্মুলাটি রেঞ্জের নাম হিসেবে ব্যবহার করা হয়েছে “=Fruit”।

➤ F5 চাপুন, তারপর ওয়ার্কশিটে যান এবং সেল D6-এর ড্রপ-ডাউন সিম্বলে ক্লিক করুন।
পরে, আপনি ফলের লিস্ট পাবেন এবং লিস্ট থেকে যেকোনো একটি আইটেম সিলেক্ট করুন যেন Blueberries।

তাহলে, আমরা লিস্ট থেকে আমাদের বাঞ্ছিত আইটেম Blueberries পেয়েছি এবং এর পাশাপাশি আমরা ফলের জন্য আমাদের তৈরি করা নেমড রেঞ্জটিও দেখতে পাচ্ছি।

সম্পর্কিত বিষয়: এক্সেলে একাধিক সিলেকশনসহ ডেটা ভ্যালিডেশন ড্রপ-ডাউন লিস্ট তৈরি করা

সাদৃশ্য পাঠ্য:

• এক্সেলে অটোকমপ্লিট ডেটা ভ্যালিডেশন ড্রপ-ডাউন লিস্ট (২ পদ্ধতি)
• ফিল্টারসহ এক্সেল ডেটা ভ্যালিডেশন ড্রপ-ডাউন লিস্ট (২ উদাহরণ)
• এক্সেল VBA-তে ডিফল্ট ভ্যালু ডেটা ভ্যালিডেশন লিস্টে (ম্যাক্রো এবং ইউজারফর্ম)
• এক্সেলে একাধিক কেরিয়ার জন্য কাস্টম ডেটা ভ্যালিডেশন প্রয়োগ করা (৪ উদাহরণ)
• এক্সেল ডেটা ভ্যালিডেশন অ্যালফানিউমেরিক অনলি (কাস্টম ফর্মুলা ব্যবহার করে)

পদ্ধতি-৩: এক্সেল VBA ব্যবহার করে নেমড রেঞ্জ দিয়ে ডেটা ভ্যালিডেশন লিস্ট আপডেট করুন

ধরুন, আমাদের সেল D6-এ নিচের ড্রপ-ডাউন লিস্ট আছে, যা ফিক্সড ডেটাসেটের জন্য ভালো কাজ করে।

কিন্তু, আমরা যদি একটি অতিরিক্ত শাক Lettuce যোগ করি তবে সেটি ড্রপ-ডাউন লিস্টে দেখাবে না যার মানে আমাদের ড্রপ-ডাউন লিস্ট এই কেসে স্বয়ংক্রিয়ভাবে আপডেট হচ্ছে না।

লিস্টটি দ্রুত ও স্বয়ংক্রিয়ভাবে আপডেট করতে আপনি এই পদ্ধতি অনুসরণ করতে পারেন।

৩.১: আপডেটেড নেমড রেঞ্জ তৈরি করা

প্রথমে, আমাদের কলাম B-এর রেঞ্জের জন্য একটি নাম যোগ করতে হবে যেন এটি নতুন যোগ করা আইটেমগুলো স্বয়ংক্রিয়ভাবে এই নেমে নিতে পারে।
➤ Formulas ট্যাব >> Defined Names গ্রুপ >> Name Manager অপশনে যান।

তারপর, Name Manager ডায়লগ বক্স খুলবে।
➤ New অপশনে ক্লিক করুন।

এরপর, Edit Name উইজার্ড পপ আপ হবে।
➤ Name বক্সে Vegetables লিখুন এবং Refers to বক্সে নিচের ফর্মুলা লিখুন এবং অবশেষে OK চাপুন।

এখানে, Update! শিটের নাম, $B$4 রেফারেন্স সেল যেখান থেকে আমরা যেতে চাই, 0 Rows এবং Columns আর্গুমেন্টের জন্য মানে এটি তার রেফারেন্স বা শুরুর পজিশনে থাকবে।
COUNTA কলাম B-এ যেকোনো টাইপের ভ্যালু সম্পন্ন সেলের সংখ্যা গণনা করবে এবং তারপর 2 বিয়োগ করা হবে কারণ ডেটাসেটের হেডিং B1-এ এবং কলামের হেডার B3-এ। তাই, আপনি শুধু শাক ধারণকারী সেলের সংখ্যা পাবেন।
এই সংখ্যাটি শুরুর পজিশন থেকে রিটার্ন রেফারেন্স হবে এবং OFFSET এখানে সর্বদা আপডেটেড নেমড রেঞ্জ ফেরত দেবে।

তারপর, আপনাকে Name Manager উইজার্ডে নিয়ে যাওয়া হবে।
➤ Close চাপুন।

৩.২: ডেটা ভ্যালিডেশন লিস্ট প্রয়োগের জন্য VBA কোড ব্যবহার করা

➤ শিটের নামের ওপর রাইট-ক্লিক করুন এবং View Code অপশন নির্বাচন করুন।

এরপর, কোড উইন্ডো দেখাবে।

➤ নিচের কোডটি টাইপ করুন

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 হলো Object এবং Change হলো Procedure।
newitem সেলের ঠিকানা ধারণ করে যেখানে আমরা নতুন ভ্যালু যোগ করছি এবং আমরা এটিকে Range হিসেবে ডিফাইন করেছি। updatedrange এবং item এর ডেটা টাইপ Variant माना जाएगा, जहां हमने updatedrange को शाक के अपडेटेड नेम्ड रेंज को असाइन किया है और item इस रेंज के प्रत्येक सेल के मूल्यों के लिए है।
FOR लूप अपडेटेड रेंज को updatedrange में असाइन करेगा और WITH स्टेटमेंट एक ही ऑब्जेक्ट की पुनरावृत्ति से बचाएगा, और अंत में, हमने validation जोड़ा है।

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

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

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

पद्धति-४: कंडीशनल ड्रॉप डाउन लिस्ट बनाने के लिए नामित सीमा का उपयोग करना

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

धापসমূহ:
➤ पद्धति-১-এর স্টেপ-০১ অনুসরণ করুন।
➤ নিচের কোডটি লিখুন

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 लिस्ट के रूप में मिलेगा।

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

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

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

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

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

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

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

उपसंहार

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

संबंधित लेख

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