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

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

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

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

حال سئوال این است که چگونه می توانیم چند انتخاب را در کنار یکدیگر داشته باشیم؟

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

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

تنها راه برای حل این مشکل استفاده از کدهای VBA می باشد به صورتی که انتخاب اول را نگهداشته و انتخاب های بعدی را به آنها اضافه کند.

این پست شامل مطالب زیر است:

چگونه در لیست های کشویی انتخاب های چند گانه داشته باشیم؟

• ایجاد لیست پایین افتادنی در اکسل
• کد VBA برای انتخاب چندگانه از لیست پایین افتادنی با تکرار (آیتمی که قبلاً انتخاب شده را مجدد بتوانیم انتخاب کنیم)
• کد VBA برای انتخاب چندگانه از لیست پایین افتادنی بدون تکرار (آیتمی که قبلاً انتخاب شده را مجدد نتوانیم انتخاب کنیم)
• کد را در کجا باید قرار دهیم؟
• سئوالات متداول

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

برای ایجاد لیست کشویی در اکسل به صورت زیر عمل کنید

• یک سلول یا ناحیه ای که قصد دارید لیست پایین افتادنی را ایجاد کنید انتخاب نمایید ( در این مثال سلول C2 )
• از سربرگ Data و از گروه Data Tools ابزار Data validation را انتخاب نمایید
• از پنجره ای که ظاهر می شود و از سربرگ Setting و در کادر زیر عنوان Allow عبارت List را انتخاب کنید
• از پنجره مرحله 3 و در کادر زیر عنوان Source ناحیه ای که قصد دارید داده های آن در لیست پایین افتادنی قرار گیرند را انتخاب کرده و بر روی OK کلیک کنید.

در این مثال ما داده های لیست پایین افتادنی را در  سلول های A1 تا A6 وارد کرده ایم

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

اکنون ما یک لیست کشویی داریم که هر بار فقط می توانیم یک آیتم را انتخاب کنیم.

برای آنکه بتوانیم در لیست کشویی ایجاد شده بیش از یک آیتم را انتخاب کنیم باید کد VBA را به فایل اضافه کنیم

کد VBA با قابلیت انتخاب چندگانه با آیتم های تکراری

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Oldvalue As String
Dim Newvalue As String

On Error GoTo Exitsub
If Target.Address = "$C$2" Then
    If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
    Else: If Target.Value = "" Then GoTo Exitsub Else
        Application.EnableEvents = False
        Newvalue = Target.Value
        Application.Undo
        Oldvalue = Target.Value
        If Oldvalue = "" Then
            Target.Value = Newvalue
        Else
            Target.Value = Oldvalue & ", " & Newvalue
        End If
    End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

در کد بالا اگر قبلاً آیتمی را از لیست کشویی انتخاب کرده باشید و بخواهید مجدد آن را انتخاب نمایید می توانید این کار را انجام دهید. یعنی تکرار در انتخاب مجاز می باشد

انتخاب چندگانه با تکرار

حال اگر قصد داشته باشیم که اجازه انتخاب آیتمی که قبلاً انتخاب شده را نداشته باشیم از کد زیر استفاده می کنیم:

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

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

انتخاب چندگانه بدون تکرار

اکنون باید کد مورد نظر را به فایل اضافه کنیم (توجه: فقط یکی از کدهای بالا را به فایل اضافه کنید)

قبل از استفاده از کد، باید آن را در مکانی قرار دهیم که با هر تغییری در انتخاب از لیست کشویی کد اجرا شود.

برای اینکه بدانیم چگونه کد را به فایل اضافه کنیم مراحل زیر را انجام دهید:

• کلیدهای ترکیبی Alt+F11 را فشار دهید تا به ادیتور ویژوال بیسیک منتقل شوید
• پنجره پروژه ها در سمت چپ و یک صفحه خالی خاکستری رنگ در سمت راست قابل مشاهده است. اگر پنجره پروژه نمایش داده نشد کلیدهای ترکیبی Ctrl+R را برای نمایش آن فشار دهید
• در پنجره پروژه ها بر روی نام شیتی که لیست کشویی را در آن ایجاد کرده اید دابل کلیک کنید.
• پس از انجام مرحله 3 پنجره کد نمایش داده میشود.
• کد مورد نظر را Copy کرده و در پنجره کد Paste  کنید
• پنجره کد را ببندید

پنجره پروژه ها

پنجره کدنویسی

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

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

سئوالات متداول

• دراین مثال کد VBA فقط برای سلول C2 نوشته شده چگونه آن را برای سلول های دیگر استفاده کنم؟

پاسخ: فرض کنید شما قصد دارید کد را برای سلول های C2-C3-C4 استفاده کنید تغییرات را به صورت زیر انجام دهید

خط زیر را پیدا کنید

If Target.Address = “$C$2” Then

به جای کد بالا از این کد استفاده کنید

If Target.Address = “$C$2” Or Target.Address = “$C$3” Or Target.Address = “$C$4” Then

• من قصد دارم لیست کشویی را در کل ستون C ایجاد کنم. چگونه این کار را انجام دهم؟

پاسخ:

خط زیر را پیدا کنید

If Target.Address = “$C$2” Then

به جای کد بالا از این کد استفاده کنید

 If Target.Column = 3 Thenمنظور از عدد 3 یعنی سومین ستون (ستون (C

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

If Target.Column = 3 or Target.Column = 4 Then

• من قصد دارم لیست کشویی را در یک ردیف ایجاد کنم (مثلاً ردیف دوم)، چگونه این کار را انجام دهم؟

پاسخ:

خط زیر را پیدا کنید

If Target.Address = “$C$2” Then

به جای کد بالا از این کد استفاده کنید

If Target.Row = 2  Then

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

If Target.Row = 2  or Target.Row = 3 Then

• در کد بالا از علامت کاما برای جداسازی آیتم های مختلف استفاده شده است چگونه از فاصله استفاده کنم؟

پاسخ:

خط کد زیر را پیدا کنید

Target.Value = Oldvalue & “, ” & Newvalue

با کد زیر جایگزین کنید

Target.Value = Oldvalue & ” ” & Newvalue

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

• آیا می توانم هر آیتم را در یک خط جداگانه در همان سلول قرار دهم؟

پاسخ: بلی این کار امکان پذیر است

خط کد زیر را پیدا کنید

Target.Value = Oldvalue & “, ” & Newvalue

با خط کد زیر جایگزین نمایید

 Target.Value = Oldvalue & vbNewLine & Newvalue

مطالعه بیشتر: لیست های کشویی چند سطحی

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