در اکسل، لیست باکس‌ها ابزارهای مفیدی هستند که به کاربران این امکان را می‌دهند تا از میان گزینه‌های موجود، انتخاب‌های مختلفی را انجام دهند. این ابزار در قالب یک کنترل گرافیکی در UserForm قرار می‌گیرد و به ویژه در مواقعی که نیاز به مدیریت و نمایش اطلاعات به‌صورت مرتب و سازمان‌یافته است، کارایی بسیاری دارد. با استفاده از کد نویسی لیست باکس در اکسل، می‌توان مقادیر مختلف را به‌صورت خودکار در آن وارد کرد، داده‌ها را از آن بازیابی کرد یا حتی محتویات آن را به‌راحتی پاک نمود.
این مقاله به بررسی نحوه استفاده از کد نویسی لیست باکس در اکسل، پر کردن و مدیریت آن با استفاده از VBA پرداخته و تفاوت‌های آن با کامبو باکس را نیز شرح می‌دهد تا کاربران بتوانند از این ابزار قدرتمند در پروژه‌های خود بهره‌برداری کنند.
برای آموزش کد نویسی در اکسل می‌توانید مقاله قبلی ما با عنوان برنامه نویسی در اکسل را مطالعه کنید.
لیست باکس در VBA اکسل چیست؟
کد نویسی لیست باکس در اکسل به معنی ایجاد و مدیریت لیست‌هایی است که کاربران می‌توانند از میان گزینه‌های موجود، یکی یا چند مورد را انتخاب کنند. لیست باکس در VBA اکسل یک لیست است که به یک متغیر اختصاص داده می‌شود. این لیست دارای ورودی‌های مختلفی برای انتخاب است و امکان انتخاب چندین گزینه به‌طور همزمان را فراهم می‌کند. یک لیست باکس را می‌توان با انتخاب گزینه لیست باکس در UserForm درج کرد.
لیست باکس معمولاً برای نمایش مجموعه‌ای از داده‌ها استفاده می‌شود و می‌تواند از محدوده‌های مشخص‌شده در شیت اکسل مقدار بگیرد. این قابلیت به کاربران کمک می‌کند تا بدون نیاز به وارد کردن داده‌ها به‌صورت دستی، از بین گزینه‌های موجود انتخاب کنند. با کدنویسی مناسب، می‌توان لیست باکس را به‌صورت پویا تنظیم کرد تا بر اساس شرایط مختلف تغییر کند.
نحوه ایجاد لیست باکس در اکسل
مراحل ایجاد لیست باکس در اکسل به شرح زیر است:
مرحله ۱: فعال کردن تب Developer در نوار ابزار اکسل برای دسترسی به کنترل‌های ActiveX. در پنجره Excel Options، روی Customize Ribbon کلیک کرده و گزینه Developer را انتخاب کنید.
مرحله ۲: در تب Developer، روی Insert کلیک کرده و سپس کنترل List Box را انتخاب کنید. با این کار یک لیست باکس جدید در برگه اکسل درج می‌شود.
مرحله ۳: یک مستطیل در برگه اکسل رسم کنید تا لیست باکس ایجاد شود، همان‌طور که در تصویر زیر نشان داده شده است.
مرحله ۴: لیست باکس را انتخاب کنید، به تب Developer بروید و گزینه Properties را انتخاب کنید. پنجره Properties باز می‌شود.
مرحله ۵: در پنجره Properties، مقدار days را در قسمت ListFillRange وارد کنید. days محدوده نام‌گذاری‌شده‌ای برای روزهای هفته است.
مرحله ۶: روی ویژگی MultiSelect کلیک کنید. از لیست کشویی، گزینه ۱- fmMultiSelectMulti را انتخاب کنید، همان‌طور که در تصویر بعدی نشان داده شده است.
مرحله ۷: روی ویژگی ListStyle کلیک کنید. از لیست کشویی، گزینه ۱- fmListStyleOption را انتخاب کنید. این گزینه، چک‌باکس‌هایی را در سمت چپ موارد لیست‌شده اضافه می‌کند، همان‌طور که در تصویر زیر نشان داده شده است.
مرحله ۸: پنجره Properties را ببندید. لیست باکس به شکل نشان داده‌شده در تصویر ظاهر می‌شود.
نکته: می‌توانید با کلیک روی Design Mode گزینه Design Mode Off را انتخاب کنید. این کار حالت طراحی را غیرفعال می‌کند.
کد نویسی لیست باکس در اکسل با VBA
برای استفاده از کد نویسی لیست باکس در اکسل و نمایش داده‌ها به‌صورت دینامیک، باید ابتدا یک لیست باکس ایجاد کرده و آن را به محدوده‌ای از داده‌ها متصل کنیم. در ادامه این مقاله نحوه ایجاد و تنظیم لیست باکس را در VBA اکسل را توضیح می‌دهیم.
مراحل ایجاد لیست باکس در VBA
مرحله ۱: روی لیست باکس راست‌کلیک کنید و اطمینان حاصل کنید که Design Mode فعال است. این کار لیست باکس را به یک سلول متصل می‌کند.
مرحله ۲: در پنجره Properties، ویژگی LinkedCell را پیدا کرده و مقدار B17 را وارد کنید، همان‌طور که در تصویر زیر نشان داده شده است.
مرحله ۳: در ویژگی ListFillRange مقدار Month را وارد کنید.
مرحله ۴: لیست باکس همان‌طور که در تصویر بعدی نشان داده شده است، ظاهر می‌شود.
🔹 نکته: مقدار در سلول B17 هنگام تغییر انتخاب در لیست باکس تغییر خواهد کرد.
نحوه ایجاد لیست باکس در UserForm در VBA اکسل
در کدنویسی لیست باکس در اکسل با VBA، می‌توان لیست باکس را در یک UserForm ایجاد کرد تا کاربران بتوانند به‌راحتی داده‌ها را مشاهده و انتخاب کنند. در ادامه، مراحل ایجاد و مقداردهی لیست باکس در UserForm را بررسی می‌کنیم.
مراحل ایجاد لیست باکس در UserForm
مرحله ۱: در تب Developer، روی Visual Basic کلیک کنید یا کلیدهای Alt + F11 را فشار دهید.
مرحله ۲: از منوی Insert، گزینه UserForm را انتخاب کنید.
مرحله ۳: از Toolbox، کنترل List Box را روی UserForm بکشید و رها کنید.
مرحله ۴: روی UserForm دوبار کلیک کنید و رویداد UserForm را انتخاب کنید، همان‌طور که در تصویر زیر نشان داده شده است.
مرحله ۵: کد زیر را در بخش مربوطه اضافه کنید.
Private Sub UserForm_Initialize() ListBox1.AddItem "MBA" ListBox1.AddItem "MCA" ListBox1.AddItem "MSC" ListBox1.AddItem "MECS" ListBox1.AddItem "CA" End Sub
مرحله ۶: کلید F5 را فشار دهید تا کد اجرا شود.
نحوه افزودن کنترل داینامیک به لیست باکس
در کد نویسی لیست باکس در اکسل با VBA، گاهی اوقات نیاز داریم که لیست باکس‌ها به‌صورت داینامیک و بر اساس تعامل کاربر ساخته شوند. این می‌تواند به‌وسیله‌ی CommandButton و در حین اجرای برنامه انجام شود. در ادامه نحوه افزودن یک کنترل داینامیک به لیست باکس در UserForm را توضیح می‌دهیم.
مراحل افزودن کنترل داینامیک به لیست باکس در UserForm با استفاده از VBA:
مرحله ۱: از Toolbox، یک CommandButton را به UserForm اضافه کنید.
مرحله ۲: روی CommandButton راست‌کلیک کنید و گزینه Properties را انتخاب کنید.
مرحله ۳: مقدار Caption را در پنجره Properties به Create_Listbox تغییر دهید.
مرحله ۴: روی CommandButton دوبار کلیک کنید. کد زیر ظاهر می‌شود.
مرحله ۵: نام رویه (Procedure) را Add_Dynamic_Listbox قرار دهید.
Sub Add_Dynamic_Listbox()
'Add Dynamic List Box and assign it to object 'LstBx'
Set LstBx = UserForm3.Controls.Add("Forms.ListBox.1")
'List Box Position
LstBx.Left = 20
LstBx.Top = 10
End Sub
مرحله ۶: کلید F5 را فشار دهید تا ماکرو اجرا شود.
نحوه افزودن آیتم‌ها به کنترل لیست باکس در VBA
در کد نویسی لیست باکس در اکسل با VBA، می‌توان آیتم‌های مختلفی را به لیست باکس اضافه کرد. این کار می‌تواند به‌صورت دستی یا با دریافت داده از محدوده‌ای در شیت اکسل انجام شود. در ادامه یک نمونه کد برای افزودن آیتم‌ها به لیست باکس آورده شده است:
Private Sub Insert_Items_To_LstBox()
ListBox1.AddItem "Item 1"
ListBox1.AddItem "Item 2"
ListBox1.AddItem "Item 3"
ListBox1.AddItem "Item 4"
ListBox1.AddItem "Item 5"
End Sub
توضیح کد: ابتدا یک تابع خصوصی (Private Sub) ایجاد شده که هنگام اجرا، آیتم‌های مشخص‌شده را به لیست باکس (ListBox1) اضافه می‌کند. با استفاده از متد AddItem، هر آیتم به لیست باکس اضافه می‌شود.
روش‌های دیگر برای افزودن آیتم‌ها به لیست باکس در اکسل با VBA:
دریافت داده از شیت اکسل: اگر بخواهید داده‌ها را از محدوده‌ای مشخص در شیت اکسل دریافت کنید، می‌توانید از حلقه استفاده کنید:
Private Sub Insert_Items_From_Sheet()
Dim i As Integer
For i = 1 To 5
ListBox1.AddItem Sheets("Sheet1").Cells(i, 1).Value
Next i
End Sub
در این کد، مقادیر ستون اول (A1 تا A5) از شیت Sheet1 خوانده شده و به لیست باکس اضافه می‌شود.
نحوه پاک کردن آیتم‌ها از کنترل لیست باکس با استفاده از VBA
گاهی اوقات نیاز داریم آیتم‌های موجود در لیست باکس را پاک کنیم، مخصوصاً زمانی که داده‌ها تغییر می‌کنند یا نیاز به بارگذاری مجدد اطلاعات داریم.
چرا باید لیست باکس را پاک کنیم؟
- به‌روزرسانی داده‌ها: هنگام تغییر اطلاعات، پاک کردن لیست باکس و بارگذاری مجدد آن ضروری است.
- مدیریت بهتر رابط کاربری: اگر کاربر قبلاً داده‌ای انتخاب کرده باشد، می‌توان لیست را برای ورود اطلاعات جدید پاک کرد.
برای حذف تمام آیتم‌های یک لیست باکس، می‌توان از دستور Clear استفاده کرد به صورت زیر استفاده کرد:
Sub Clr_LstBx()
UserForm3.ListBox1.Clear
End Sub
مثال: فرض کنید داده‌های فروش روزانه داریم. می‌خواهیم یک رابط کاربری گرافیکی برای این داده‌های فروش ایجاد کنیم که به ما امکان دهد آیتم‌هایی را از لیست باکس انتخاب کنیم. برای دریافت جزئیات فروش، باید سلول ایندکس را به cell link تغییر داده و فرمول VLOOKUP را اعمال کنیم، همان‌طور که در تصاویر زیر نشان داده شده است.
چگونه از لیست باکس در اکسل استفاده کنیم؟
لیست باکس یکی از ابزارهای مفید در اکسل است که برای نمایش و مدیریت داده‌ها در UserForm استفاده می‌شود. این ابزار به کاربران امکان می‌دهد که از میان چندین گزینه، یک یا چند مورد را انتخاب کنند.
ویژگی‌های لیست باکس:
- لیست باکس به مرتب‌سازی داده‌ها کمک می‌کند و از نمایش اطلاعات غیرضروری جلوگیری می‌کند.
- کاربران می‌توانند بین آیتم‌های لیست حرکت کنند و گزینه موردنظر را انتخاب کنند.
- بسته به تنظیمات، کاربران می‌توانند یک یا چند گزینه را همزمان انتخاب کنند.
- پس از انتخاب یک آیتم، می‌توان عملیاتی را بر اساس آن انجام داد.
لیست باکس به‌طور پیش‌فرض مقدار ایندکس آیتم انتخاب‌شده را نمایش می‌دهد. به این معنا که اگر گزینه چهارم در لیست انتخاب شود، مقدار خروجی عدد ۴ خواهد بود.
چگونه داده‌ها را از لیست باکس در اکسل بازیابی کنیم؟
داده‌ها تنها در صورتی به‌طور مؤثر استفاده می‌شوند که به درستی بازیابی شوند. مراحل بازیابی داده‌ها از لیست باکس به شرح زیر است:
- پس از راست‌کلیک کردن روی لیست باکس، گزینه Format Control را انتخاب کنید.
- در جعبه‌ای که با عنوان Cell Link مشخص است، مختصات یک سلول خالی را تایپ کنید.
- سلولی را انتخاب کنید که داده‌ها از لیست باکس در آن نمایش داده می‌شوند. باید از فرمول زیر استفاده کنید:
"=INDEX(:,,0)"
نکته: Cell Link همان سلولی است که در مرحله دوم مشخص شده است. مقدار نمایش داده‌شده در سلول، به انتخاب کاربر در لیست باکس بستگی دارد. اولین مقدار لیست به اولین سلول اختصاص داده می‌شود و آخرین مقدار به آخرین سلول.
با انجام این مراحل، داده‌های لیست باکس به‌راحتی در سلول‌های اکسل نمایش داده شده و می‌توان از آن‌ها برای تحلیل‌های مختلف استفاده کرد.
تفاوت بین لیست باکس و کمبو باکس در کد نویسی اکسل
در کد نویسی لیست باکس در اکسل، ممکن است این سؤال پیش آید که چه تفاوتی بین لیست باکس و کمبو باکس وجود دارد. تفاوت‌های اصلی بین این دو به شرح زیر است:
- لیست باکس شامل یک لیست از آیتم‌ها است و به کاربران اجازه می‌دهد تا یک یا چند گزینه را انتخاب کنند. در مقابل، کامبو باکس ترکیبی از یک جعبه متن و یک لیست کشویی است. جعبه متن به کاربران این امکان را می‌دهد که یک آیتم تایپ کنند. لیست کشویی به کاربران اجازه می‌دهد یک آیتم را انتخاب کنند.
- لیست باکس تمام آیتم‌ها را در یک منطقه به‌طور همزمان نمایش می‌دهد، در حالی که کامبو باکس تنها یک آیتم را در هر بار نمایش می‌دهد. آیتم‌های لیست کشویی کامبو باکس تنها زمانی قابل مشاهده هستند که کاربر روی آن کلیک کند.
- در لیست باکس، کاربر باید از میان آیتم‌های موجود انتخاب کند، در حالی که در کامبو باکس، کاربر می‌تواند یک آیتم که در لیست نیست را تایپ کند.
- لیست باکس در مواقعی استفاده می‌شود که ورودی محدود به آیتم‌های موجود در لیست باشد، در حالی که کامبو باکس در مواقعی استفاده می‌شود که لیستی از انتخاب‌های پیشنهادی نمایش داده می‌شود.
- لیست باکس فضای بیشتری را می‌گیرد زیرا تمام لیست به‌طور همزمان نمایش داده می‌شود، در حالی که کامبو باکس فضای کمتری را اشغال می‌کند زیرا لیست تنها با کلیک روی فلش پایین نمایش داده می‌شود.
نتیجه‌گیری
کد نویسی لیست باکس در اکسل ابزاری کارآمد و انعطاف‌پذیر است که به کاربران این امکان را می‌دهد تا داده‌ها را به‌طور مؤثر و سازمان‌یافته مدیریت کنند. با استفاده از VBA، کاربران قادر خواهند بود لیست باکس‌ها را به‌طور داینامیک پر کنند، داده‌ها را از آن استخراج کرده و یا محتویات آن را پاک کنند. همچنین، تفاوت‌های میان لیست باکس و کامبو باکس به کاربران کمک می‌کند تا بسته به نیاز خود از ابزار مناسب استفاده کنند. در نهایت، آشنایی با کد نویسی لیست باکس در اکسل باعث می‌شود که کاربران بتوانند تجربه کاربری بهتری را ارائه دهند و کارایی تحلیل و پردازش داده‌ها را در محیط اکسل به‌طور چشمگیری افزایش دهند.
