لیست کشویی با چک باکس

لیست کشویی 

لیست های کشویی در اکسل بسیار رایج و پرکاربرد هستند اما مشکل اینجاست که کاربردر هر زمان فقط می تواند یک گزینه از لیست کشویی را انتخاب کند.

در این پست برای حل این مشکل روشی بسیار جالب را خدمت شما دوستان عزیز ارائه خواهم کرد.

هدف این است که با کلیک بر روی هر سلول از ناحیه ای که برای لیست کشویی تعریف شده است فرمی ظاهر شود و کاربر به دلخواه بتواند با تیک زدن کنار هر کدام از گزینه ها، آنها را انتخاب و یا از حالت انتخاب خارج کند.

برای این منظور فرمی طراحی می شود و در این فرم چهار دکمه قرار داده می شود که عبارتند از: OK برای درج موارد انتخاب شده در سلول، دکمه ALL برای انتخاب کلیه گزینه های لیست کشویی، دکمه Clear برای حذف موارد انتخاب شده

و در نهایت دکمه Cancel برای لغو انتخاب ها.

نتیجه نهایی به شکل زیر خواهد بود.

شکل 1

 کدهای مورد نیاز برای هربخش

برای انجام این کار باید با کدنویسی VBA آشنایی داشته باشیم (اگر با کدنویسی آشنایی ندارید نگران نباشید، تمام کدهای لازم برای انجام این کار را با توضیحات کافی برای شما نوشته ایم)

خلاصه مراحل انجام کار:

• ایجاد یک فایل اکسل و ذخیره آن به صورت Macro Enablel Workbook (پسوند فایل باید xlsm باشد)
• اضافه کردن کاربرگ جدید و تغییر نام آن به List (در نسخه های جدید آفیس فایل ایجاد شده به صورت پیش فرض دارای یک کاربرگ به نام sheet1 می باشد و باید یک کاربرگ جدید اضافه کنیم و نام آن را به List تغییر می دهیم)
• در کاربرگ List و در ستون ) 1ستون A) اقلامی که در لیست کشویی باید قرار بگیرند را وارد می کنیم (در این مقاله ما اسم کشورها را در ردیف های 1 تا 5 وارد کرده ایم)
• برای ناحیه ای که اسامی کشورها قرار دارند نامی انتخاب می کنیم (در اینجا محدوده A1:A5 اسامی کشورها قرار دارند که برای آن نام Countries انتخاب شده است)
• در کاربرگ sheet1 ، ستون A را انتخاب می کنیم و در آن لیست کشویی ایجاد می کنیم. اطلاعات لیست کشویی باید نام کشورها باشند که در کاربرگ List قرار گرفته اند
• سربرگ Developer را فعال می کنیم
• به محیط کدنویسی وارد می شویم و یک یوزرفرم را ایجاد می کنیم
• بر روی یوزرفرم یک لیست باکس و چهار دکمه قرار می دهیم
• خواص یوزرفرم و اسامی دکمه ها را تنظیم می کنیم
• یک ماژول ایجاد می کنیم
• کدهای لازم برای هر کدام از موارد فوق را می نویسیم (کدهای sheet1 – UserForm- Module)

جزئیات گام های انجام کار

• ابتدا اکسل را باز کنید و آن را با صورت macro-enabled ذخیره کنید

File -> Save As -> Excel Macro-Enabled Workbook (*.xlsm)

شکل 2

ذخیره فایل به صورت macro-enabled ضروری است، در غیر این صورت کدها کار نخواهند کرد.

• سربرگ Developer را به اکسل اضافه کنید (این سربرگ در حالت عادی فعال نیست و باید طبق روش زیر آن را به نوارابزار اکسل اضافه کنید)

فعال کردن نوار ابزار Developer

بر روی File در کاربرگ اکسل کلیک کنید

پنجره ای ظاهر می شود که در انتهای آن عبارت Options دیده می شود.

بر روی Options کلیک کنید تا پنجره تنظیمات آن باز شود.

از قسمت چپ پنجره بر روی Customize Ribbon کلیک کنید.

دو پنجره ظاهر می شوند که از پنجره سمت راست تیک کنار گزینه Developer را فعال کنید

شکل های زیر مراحل انجام کار را نمایش می دهد

شکل 3

دسترسی به محیط کدنویسی (Alt+F11)

شکل 6

وقتی که سربرگ Developer به اکسل اضافه شد بر روی آن کلیک کنید تا به ابزارهای آن دسترسی داشته باشید.

در گوشه بالا و سمت چپ عبارت Visual Basic دیده می شود. اگر بر روی آن کلیک کنید به پنجره کدنویسی وارد می شوید.

یا می توایند از کلیدهای ترکیبی Alt+F11 برای دسترسی به پنجره کدنویسی استفاده کنید

شکل 7

ساخت UserForm

در پنجره کدنویسی بر روی سربرگ Insert کلیک کرده و UserForm را انتخاب کنید

تا یک فرم خام به نام UserForm1 ایجاد شود

شکل 8

از این فرم برای گرفتن اطلاعات از کاربر استفاده می شود.

ایجاد لیست باکس

گام بعدی اضافه کردن یک لیست باکس به یوزرفرم ایجاد شده است
برای انجام این کار باید از پنجره ToolBox استفاده کنید

تذکر: اگر پنجره ToolBox دیده نمی شود مطابق شکل زیربر روی ابزار ToolBox کلیک کنید

شکل 9

از پنجره ToolBox بر روی ListBox کلیک کرده و آن را بر روی فرم بکشید (درگ کنید)

شکل 10

شکل 11

اندازه ListBox را مطابق نیاز خود تنظیم کنید

ایجاد دکمه ها

اکنون باید Command button یا دکمه ها را به فرم اضافه کنیم. مانند روش قبل از پنجره ToolBox بر روی command button کلیک کرده و بر روی فرم بکشید

شکل 12

شکل 13

برای تغییر نام اولین دکمه به cancel آن را انتخاب کنید و از لیست خصوصیات (کادر سمت چپ) مقابل عبارت Caption کلمه CommandButton1 را با کلمه Cancel جایگزین کنید  و سایز دکمه روی فرم را به دلخواه تنظیم نمایید.

سپس سه دکمه دیگر به نام های Clear, All, Ok ایجاد کنید

اکنون فرم ایجاد شده باید مطابق تصویر زیر باشد

شکل 14

تنظیم خواص لیست باکس

لیست باکس را انتخاب کنید. مطابق شکل نام ListBox1 Listbox و پنجره خصوصیات آن نمایش داده می شود.

شکل 15

این خصوصیات مربوط به شیء انتخاب شده هستند. ازقبیل  لیست باکس- دکمه و یوزرفرم

اکنون باید از لیست خصوصیات لیست باکس عبارت List Style را انتخاب کرده و در کادر مقابل آن عبارت 1-fmListStyleOption را انتخاب کنیم

شکل 16

به روشی مشابه خصوصیت MultiSelect را به 1-fmMultiSelectMulti تغییردهیم.
بعد از تنظیم خصوصیات لیست باکس باید کدهای مورد نظر را بنویسیم

کدهای مورد نیاز

از ناحیه VBAProject و از گوشه بالا و سمت چپ بر روی نام Sheet1 دابل کلیک کنید تا به پنجره کدنویسی وارد شوید

کدهای زیر را کپی کرده و در پنجره کد قرار دهید

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.EnableEvents = False

On Error GoTo exitHandler

If Target.Column = 1 Then

UserForm1.Show ‘Pop up the form

End If

exitHandler:

Application.EnableEvents = True

End Sub

شکل 17

اکنون باید یک ماژول اضافه کنیم. اضافه کردن ماژول دقیقاً مانند اضافه کردن فرم است فقط به جای UserForm باید از سربرگ Insert گزینه Module را انتخاب کنیم.
پنجره کدنویسی دیگری باز می شود. کدهای زیر را کپی و در محیط کدنویسی قرار دهید

()Sub enEvents

Application.EnableEvents = True

End Sub

کد نوشته شده در بالا باعث می شود در هر سلولی از ستون 1 (ستون (A در Sheet 1  که کلیک کنیم UserForm1 ظاهر گردد. به فایل اکسل بروید و در ستون 1 در هر سلول دلخواهی کلیک کنید. باید شکلی مشابه تصویر زیر داشته باشید

شکل 18

اگر با کلیک در ستون 1 یوزرفرم ظاهر نشد به سربرگ Developer رفته و سپس بر بروی Macros کلیک کنید و از پنجره ای که ظاهر می شود ماکروی enEvents را اجرا کنید

شکل 19

تا اینجا شما یک پنجره ایجاد کرده اید که با کلیک در هر سلول از ستون مربوطه فراخوانی می گردد اما کاری انجام نمی دهد. حتی اگر بر روی دکمه ها کلیک کنید هیچ اتفاقی نمی افتد

اکنون باید اطلاعات لیست کشویی را در شیت جدیدی به نام List ایجاد کرده و لیست کشویی را در Sheet 1  ستون A قرار دهیم

کاربرگ ثبت اطلاعات

برای انجام این کار شیت دیگری ایجاد کرده و لیست را در آن ذخیره می کنیم.  اسم شیت جدید را List گذاشته ام.

شکل 20

برای انجام شیت جدید بر روی علامت + کلیک کنید تا شیت جدید با نام sheet2 ایجاد گردد. سپس بر روی نام آن دابل کلیک کرده و اسم آن را به List تغییر دهید.

فرض ما بر این است که لیستی از اسامی کشورها را را ایجاد کرده و قصد داریم چندین انتخاب از این لیست داشته باشیم

نام کشورها را در شیت “List “ و در ستون A وارد کنید

شکل 22

نامگذاری محدوده اطلاعات

برای ناحیه ای که اسامی کشورها قرار گرفته نامی اختصاص می دهیم.

برای نامگذاری محدوده،  نام کشورها از ردیف 1 تا 5 را انتخاب کرده و نام Countries  را به آن اختصاص می دهیم.

جهت اختصاص نام می توانید ازسربرگ  Formulas و سپس انتخاب Define Name   نام دلخواهی را وارد کنید. در این مثال ما از نام Countries استفاده کرده ایم.

به جای انتخاب مسیر قبلی می توانید مستقیماً نام مورد نظر را در کادر name box) ) در گوشه بالا و سمت چپ وارد کنید

تذکر: در نامگذاری نباید از فاصله خالی استفاده کنید بلکه به جای آن از زیر خط (Underline) استفاده کنید.

شکل 23

ایجاد لیست کشویی

به Sheet 1 برگردید و ستون 1 را انتخاب کنید.

اگر پنجره فرم باز شد آن را ببندید. بعد از انتخاب ستون اول به سربرگ Data رفته و data Validation  را انتخاب کنید.

از پنجره ای که ظاهر می شود از سربرگ Setting و از کادر زیر عنوان Allow عنوان List را انتخاب کرده و در کادر Source بعد از قرار دادن علامت = اسامی کشورها (Countries)  را بنویسید.

به املای صحیح نام محدوده کشورها یعنی Countries دقت کنید. در صورت تایپ اشتباه نتایج نشان داده نمی شود.

شکل 24

شکل 25

با انجام این کار ستون 1 (A)  به صورت لیستی یکتا از اسامی کشورها تبدیل می شود.

هدف ما این است که بتوانیم از لیست ظاهر شده چند انتخاب داشته باشیم

برای این منظور کدهای زیر را به ماژول نوشته شده قبلی اضافه کنید

یعنی بر روی Module1 کلیک راست کرده و سپس View Code را انتخاب کنید

Option Explicit

Global gCountryListArr As Variant

Global gCellCurrVal As String

()Sub enEvents

Application.EnableEvents = True

End Sub

شکل 26

از مسیر زیر کدهای نوشته شده را به ماژول شیت 1 اضافه کنید

با راست کلیک بر روی Sheet1 و انتخاب گزینه View Code کدهای زیر را کپی و در ناحیه کدنویسی وارد کنید

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.EnableEvents = False

On Error GoTo exitHandler

If Target.Column = 1 Then

gCountryListArr = Sheets(“Lists”).Range(“A1:A9”).Value

gCellCurrVal = Target.Value

UserForm1.Show ‘Pop up the form

Target.Value = gCellCurrVal

End If

exitHandler:

Application.EnableEvents = True

End Sub

شکل 27

روی یوزر فرم راست کلیک کرده و سپس View Code را انتخاب کنید و کدهای زیر را  در آنجا قراردهید

با راست کلیک بر روی Userform1 و انتخاب گزینه View Code کدهای زیر را کپی و در ناحیه کدنویسی وارد کنید

()Private Sub CommandButton1_Click

UserForm1.Hide ‘Pop up the form

End Sub

()Private Sub CommandButton2_Click

For ii = 0 To ListBox1.ListCount – 1

Me.ListBox1.Selected(ii) = False

Next ii

End Sub

()Private Sub CommandButton3_Click

For ii = 0 To ListBox1.ListCount – 1

Me.ListBox1.Selected(ii) = True

Next ii

End Sub

()Private Sub CommandButton4_Click

gCellCurrVal = “”

For ii = 0 To ListBox1.ListCount – 1

If Me.ListBox1.Selected(ii) = True Then

If gCellCurrVal = “” Then

gCellCurrVal = Me.ListBox1.List(ii)

Else

gCellCurrVal = gCellCurrVal & “,” & Me.ListBox1.List(ii)

End If

End If

Next ii

UserForm1.Hide

End Sub

Private Sub ListBox1_Click()

End Sub

()Private Sub UserForm_Activate

On Error Resume Next

Me.ListBox1.Clear

For Each element In gCountryListArr

Me.ListBox1.AddItem element

Next element

UserForm_Initialize

End Sub

()Private Sub UserForm_Initialize

For Each element In Split(gCellCurrVal, “,”)

For ii = 0 To ListBox1.ListCount – 1

If element = Me.ListBox1.List(ii) Then

Me.ListBox1.Selected(ii) = True

End If

Next ii

Next element

End Sub

مقالات مرتبط با این پست

استفاده از لیست باکس برای ایجاد لیست کشویی با انتخاب چند گانه

لیست های پایین افتادنی

در اینجا کار به اتمام رسیده و با کلیک بر روی هر سلول از ستون A می توانید داده های مورد نظر را انتخاب کرده و در همان سلول درج نمایید

دریافت فایل این مقاله

برای هر کدام از اجزاء فرم از کدهای این قسمت استفاده نمایید

کد شیت 1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.EnableEvents = False

On Error GoTo exitHandler

If Target.Column = 1 Then

gCountryListArr = Sheets(“List”).Range(“A1:A5”).Value

gCellCurrVal = Target.Value

UserForm1.Show ‘Pop up the form

Target.Value = gCellCurrVal

End If

exitHandler:

Application.EnableEvents = True

End Sub

کد ماژول 1

Option Explicit

Global gCountryListArr As Variant

Global gCellCurrVal As String

Sub enEvents()

Application.EnableEvents = True

End Sub

کد یوزرفرم

Private Sub CommandButton1_Click()

UserForm1.Hide ‘Pop up the form

End Sub

Private Sub CommandButton2_Click()

For ii = 0 To ListBox1.ListCount – 1

Me.ListBox1.Selected(ii) = False

Next ii

End Sub

Private Sub CommandButton3_Click()

For ii = 0 To ListBox1.ListCount – 1

Me.ListBox1.Selected(ii) = True

Next ii

End Sub

Private Sub CommandButton4_Click()

gCellCurrVal = “”

For ii = 0 To ListBox1.ListCount – 1

If Me.ListBox1.Selected(ii) = True Then

If gCellCurrVal = “” Then

gCellCurrVal = Me.ListBox1.List(ii)

Else

gCellCurrVal = gCellCurrVal & “,” & Me.ListBox1.List(ii)

End If

End If

Next ii

UserForm1.Hide

End Sub

Private Sub ListBox1_Click()

End Sub

Private Sub UserForm_Activate()

On Error Resume Next

Me.ListBox1.Clear

For Each element In gCountryListArr

Me.ListBox1.AddItem element

Next element

UserForm_Initialize

End Sub

Private Sub UserForm_Initialize()

For Each element In Split(gCellCurrVal, “,”)

For ii = 0 To ListBox1.ListCount – 1

If element = Me.ListBox1.List(ii) Then

Me.ListBox1.Selected(ii) = True

End If

Next ii

Next element

End Sub