Note: The other languages of the website are Google-translated. Back to English
ログイン  \/ 
x
or
x
今すぐ登録  \/ 
x

or

Excelで複数の選択または値を含むドロップダウンリストを作成するにはどうすればよいですか?

デフォルトでは、Excelのデータ検証ドロップダウンリストから一度に選択できるアイテムはXNUMXつだけです。 以下のスクリーンショットに示すように、ドロップダウンリストから複数の選択を行う方法は? この記事の方法は、問題の解決に役立ちます。

VBAコードを使用して複数選択したドロップダウンリストを作成する
素晴らしいツールを使用して、複数の選択肢を持つドロップダウンリストを簡単に作成できます

ドロップダウンリストのその他のチュートリアル...


VBAコードを使用して複数選択したドロップダウンリストを作成する

以下のVBAコードを適用して、Excelのワークシートのドロップダウンリストから複数の選択を行うことができます。 次のようにしてください。

1.データ検証ドロップダウンリストを設定したワークシートを開き、シートタブを右クリックして選択します コードを表示 コンテキストメニューから選択します。

2。 の中に Microsoft Visual Basic for Applications ウィンドウで、以下のVBAコードをコードウィンドウにコピーします。 スクリーンショットを参照してください:

VBAコード:複数選択のドロップダウンリスト

Private Sub Worksheet_Change(ByVal Target As Range)
    'Updated by Extendoffice 2019/11/13
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    If Target.Count > 1 Then Exit Sub
    On Error Resume Next
    Set xRng = Cells.SpecialCells(xlCellTypeAllValidation)
    If xRng Is Nothing Then Exit Sub
    Application.EnableEvents = False
    If Not Application.Intersect(Target, xRng) Is Nothing Then
        xValue2 = Target.Value
        Application.Undo
        xValue1 = Target.Value
        Target.Value = xValue2
        If xValue1 <> "" Then
            If xValue2 <> "" Then
                If xValue1 = xValue2 Or _
                   InStr(1, xValue1, ", " & xValue2) Or _
                   InStr(1, xValue1, xValue2 & ",") Then
                    Target.Value = xValue1
                Else
                    Target.Value = xValue1 & ", " & xValue2
                End If
            End If
        End If
    End If
    Application.EnableEvents = True
End Sub

3。 プレス 他の + Q を閉じるためのキー Microsoft Visual Basic for Applications 窓。

これで、現在のワークシートのドロップダウンリストから複数のアイテムを選択できます。

注意:

  • 1.ドロップダウンリストで値が重複することはできません。
  • 2.ブックを閉じると、VBAコードは自動的に削除され、複数選択は使用できなくなります。 ブックをとして保存してください Excelマクロが有効なブック 将来的にコードを機能させ続けるために。

素晴らしいツールを使用して、複数の選択肢を持つドロップダウンリストを簡単に作成できます

ここで強くお勧めします 複数選択ドロップダウンリスト の特徴 Kutools for Excel あなたのために。 この機能を使用すると、ドロップダウンリストから、指定した範囲、現在のワークシート、現在のワークブック、または開いているすべてのワークブックの複数のアイテムを必要に応じて簡単に選択できます。

申請する前に Kutools for Excelについては 最初にダウンロードしてインストールします.

1。 クリック クツール > ドロップダウンリスト > 複数選択ドロップダウンリスト > 設定を行います。 スクリーンショットを参照してください:

2。 の中に ドロップダウンリスト設定の複数選択 ダイアログボックスで、次のように構成してください。

  • 2.1)適用範囲を 適用する セクション。 この場合、私は選択します 現在のワークシート から 指定された範囲 ドロップダウンリスト;
  • 2.2) テキストの方向 セクションでは、ニーズに基づいてテキストの方向を選択します。
  • 2.3) セパレータ ボックスに、複数の値を区切るために使用する区切り文字を入力します。
  • 2.4)確認してください 重複を追加しないでください 内箱 オプション ドロップダウンリストのセルに複製を作成したくない場合は、セクション。
  • 2.5)をクリックします OK ボタン。 スクリーンショットを参照してください:

3.をクリックしてください クツール > ドロップダウンリスト > 複数選択ドロップダウンリスト この機能を有効にします。

これで、現在のワークシートのドロップダウンリストまたは手順2で指定した任意のスコープから複数のアイテムを選択できます。

  このユーティリティの無料トライアル(30日)をご希望の場合は、 クリックしてダウンロードしてください、次に、上記の手順に従って操作を適用します。


関連記事:

Excelのドロップダウンリストに入力するとオートコンプリート
大きな値を持つデータ検証ドロップダウンリストがある場合は、適切なものを見つけるためだけにリストを下にスクロールするか、リストボックスに単語全体を直接入力する必要があります。 ドロップダウンリストの最初の文字を入力するときにオートコンプリートを許可する方法があれば、すべてが簡単になります。 このチュートリアルでは、問題を解決する方法を説明します。

Excelで別のブックからドロップダウンリストを作成する
ブック内のワークシート間でデータ検証ドロップダウンリストを作成するのは非常に簡単です。 しかし、データ検証に必要なリストデータが別のブックにある場合は、どうしますか? このチュートリアルでは、Excelの別のブックからドロップファウンリストを作成する方法について詳しく学習します。

Excelで検索可能なドロップダウンリストを作成する
多数の値を持つドロップダウンリストの場合、適切な値を見つけるのは簡単な作業ではありません。 以前、ドロップダウンボックスに最初の文字を入力したときにドロップダウンリストをオートコンプリートする方法を導入しました。 オートコンプリート機能に加えて、ドロップダウンリストを検索可能にして、ドロップダウンリストで適切な値を見つける作業効率を高めることもできます。 ドロップダウンリストを検索可能にするには、このチュートリアルの方法を試してください。

Excelのドロップダウンリストで値を選択すると、他のセルに自動入力されます
セル範囲B8:B14の値に基づいてドロップダウンリストを作成したとします。 ドロップダウンリストで任意の値を選択する場合、セル範囲C8:C14の対応する値が選択したセルに自動的に入力されるようにします。 問題を解決するために、このチュートリアルの方法はあなたに有利になります。

ドロップダウンリストのその他のチュートリアル...


最高のオフィス生産性ツール

Kutools for Excelはほとんどの問題を解決し、生産性を80%向上させます

  • 再利用: すばやく挿入 複雑な数式、チャート および以前に使用したものすべて。 セルを暗号化する パスワード付き。 メーリングリストを作成する そしてメールを送る...
  • スーパーフォーミュラバー (複数行のテキストと数式を簡単に編集できます); 読書レイアウト (多数のセルを簡単に読み取って編集する); フィルター範囲に貼り付け...
  • セル/行/列をマージする データを失うことなく; 分割セルコンテンツ; 重複する行/列を組み合わせる...重複セルを防止します。 範囲を比較する...
  • [複製]または[一意]を選択します 行; 空白行を選択 (すべてのセルは空です); スーパーファインドとファジーファインド 多くのワークブックで; ランダム選択...
  • 正確なコピー 数式参照を変更せずに複数のセル。 参照の自動作成 複数のシートに; 箇条書きを挿入、チェックボックスなど...
  • テキストを抽出、テキストの追加、位置による削除、 スペースを削除する; ページング小計の作成と印刷。 セルの内容とコメントを変換する...
  • スーパーフィルター (フィルタースキームを保存して他のシートに適用します); 高度な並べ替え 月/週/日、頻度など。 特殊フィルター 太字、斜体...
  • ワークブックとワークシートを組み合わせる; キー列に基づいてテーブルをマージします。 データを複数のシートに分割; xls、xlsx、PDFをバッチ変換...
  • 300以上の強力な機能。 Office / Excel2007-2019および365をサポートします。すべての言語をサポートします。 企業や組織に簡単に導入できます。 全機能30日間の無料トライアル。 60日間の返金保証。
kteタブ201905

Officeタブは、タブ付きのインターフェイスをOfficeにもたらし、作​​業をはるかに簡単にします

  • Word、Excel、PowerPointでタブ付きの編集と読み取りを有効にする、パブリッシャー、アクセス、Visioおよびプロジェクト。
  • 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
  • 生産性が50%向上し、毎日何百ものマウスクリックが減ります。
officetab下部
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    crystal · 10 months ago
    @James Hi James,
    Kutools can help you solve the problem with ease. You can download a free trial to have a try.
  • To post as a guest, your comment is unpublished.
    James · 10 months ago
    How can i get the code to allow the same selection more than once in the drop down?
    Thanks for any help
  • To post as a guest, your comment is unpublished.
    crystal · 1 years ago
    @Claudia Hi Claudia,
    The code can't be modified to solve this problem.
    You can try the Kutools feature. It meets all your needs.
  • To post as a guest, your comment is unpublished.
    Claudia · 1 years ago
    The code is working, so it adds all the choices that I have from my drop down list. However, if I have already chosen three of them, how can I remove one because I have made a mistake?
    For example, I have chosen (one, two and three) and I want to remove "two" by clicking again "two" from the drop down list. It allows no repetition and remove this one.
  • To post as a guest, your comment is unpublished.
    crystal · 1 years ago
    @raguirre76@gmail.com Hi Robert,
    I have tested the code but haven't found the problem as you mentioned. Can you tell me which Excel version are you using? Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    raguirre76@gmail.com · 1 years ago
    I know this may be a total random, but I use a variation of the VBA with no issue. Except on one page, if you select the first three options, it will not let you select the fourth. It will let you select the 5th, 6th, and so on, just not the 4th option. Thoughts?
  • To post as a guest, your comment is unpublished.
    Okot · 1 years ago
    I cannot proceed with creation of the multiple selection of the drop down list. I have listen to tutorial and read material but still unable to create. Kindly can someone help me out?
  • To post as a guest, your comment is unpublished.
    Eni · 1 years ago
    Hi, ich bin totaler VBA Laie. Ich versuche den Code so zu modifizieren, dass
    a) die Mehrfachauswahl nicht in allen, sondern nur ein zwei Spalten aktiv ist
    b) ich Items auch wieder rausnehmen kann, zB in dem ich in der Listenauswahl das Item noch einmal anklicke (Beispiel: ich habe über die Mehrfachauswahl ausgewählt: A, D, X, Y... nun fällt mir auf, dass D nicht dazu gehört. Beim aktuellen Code müsste ich Eingaben entfernen und neu auswählen).
    Danke im Voraus!
  • To post as a guest, your comment is unpublished.
    wendy · 1 years ago
    I'm using the code below to allow multi-select on multiple worksheets but when I go to another worksheet in the workbook the multi-select goes away. When I save the file and come back in it will work for one tab with the code but again when I click on another tab with the code it no longer works. Any idea how to fix it so if i click on a worksheet with the VBA code it will always allow multi-select?
  • To post as a guest, your comment is unpublished.
    Dez · 3 years ago
    @crystal I have the same question. My drop down list does not remember values selected. If someone clicks on a cell that has already been populated (not by them, but someone else) the selected values are cleared and the cell is blank again.
  • To post as a guest, your comment is unpublished.
    yesenia · 3 years ago
    @Nancy the cells are most likely locked, right click on all of them, go to format cells, protection, then uncheck the locked cell option
  • To post as a guest, your comment is unpublished.
    Dhina · 3 years ago
    @Mervyn If Target.Column <> 34 Then Exit Sub

    'Put this code at the beginning after your dim statements
  • To post as a guest, your comment is unpublished.
    crystal · 3 years ago
    @Randy Dear Randy,
    What do you mean "when I click on a value that has already been entered it removes it from the cell?"
  • To post as a guest, your comment is unpublished.
    Randy · 3 years ago
    I'm trying to create 4 columns with drop down lists where I can select multiple values. How do I modify the "drop down list with multiple selections" VBA code so that when I click on a value that has already been entered it removes it from the cell? Thank you in advance.
  • To post as a guest, your comment is unpublished.
    crystal · 3 years ago
    @Johnna Good Day,
    Sorry can't help you solve this problem. Please let me know if you find the answer.
  • To post as a guest, your comment is unpublished.
    Johnna · 3 years ago
    I created a drop down list where multiple text selections could be chosen such as "nutrition" ,"weight", and "work" for each caller's reason to phone in. I have a summary page where I want to see how many of each reason were indicated in a particular month. What formula would I use to tell Excel to pull out and tally each of these separately in a given month? Currently, the way I have it set up, it only tallies correctly if I have one reason in the cell for each caller.
  • To post as a guest, your comment is unpublished.
    lthompson@alz.org · 3 years ago
    @Nancy I'm having the same problem.
  • To post as a guest, your comment is unpublished.
    Nancy · 3 years ago
    I managed to use this code and successfully create multiple selection drop down boxes. It worked when I closed and re-opened on different days. However, now not all of the cells I originally selected are allowing multiple selection. Only ones done previously, despite using the code for the whole spreadsheet. Can you help?
  • To post as a guest, your comment is unpublished.
    StPaulSue · 3 years ago
    @Ramon delete the content in the cell, then reselect
  • To post as a guest, your comment is unpublished.
    Desiree · 3 years ago
    Hi all,

    I could do my drop down list perfectly, but my question is: when I select all the items nedded it goes one after another in an horizontal way through the cell, for example: yellow, green, black, red. But how can I make it look in a vertical way?, more like for example: Orange
    blanck
    yellow
    Red
    Because in horizontal the cell becomes pretty long when selecting lots of items.

    Could you please tell me if there's any way to do this?.

    Thank you,

    Desiree
  • To post as a guest, your comment is unpublished.
    Chloe · 4 years ago
    Hi all,

    I have this code on an excel sheet and its cleaning the contents from the drop down list when the cell is selected - I know what part of the code is doing it (the part that says 'fillRng.ClearContents') and I have tried to use some of the above to fix it unsuccessfully... I am new to VBA programming etc. Can anyone offer any help on how to change it so that it when the cell is selected it doesn't clear and entries wont be duplicated please??

    Option Explicit
    Dim fillRng As Range
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim Qualifiers As MSForms.ListBox
    Dim LBobj As OLEObject
    Dim i As Long

    Set LBobj = Me.OLEObjects("ListBox1")
    Set Qualifiers = LBobj.Object

    If Target.Row > 3 And Target.Column = 3 Then
    Set fillRng = Target
    With LBobj
    .Left = fillRng.Left
    .Top = fillRng.Top
    .Width = fillRng.Width
    .Height = 155
    .Visible = True
    End With
    Else
    LBobj.Visible = False
    If Not fillRng Is Nothing Then
    fillRng.ClearContents
    With Qualifiers
    If .ListCount 0 Then
    For i = 0 To .ListCount - 1
    If fillRng.Value = "" Then
    If .Selected(i) Then fillRng.Value = .List(i)
    Else
    If .Selected(i) Then fillRng.Value = _
    fillRng.Value & ", " & .List(i)
    End If
    Next
    End If
    For i = 0 To .ListCount - 1
    .Selected(i) = False
    Next
    End With
    Set fillRng = Nothing
    End If
    End If

    End Sub
  • To post as a guest, your comment is unpublished.
    Nirmala · 4 years ago
    @Charity [quote name="Charity"]This works well, but I am unable to remove an item once selected. Any suggestions in case I click on something accidently and need to remove it without (hopefully) clearing the whole cell and starting over?

    Also, for those seeking to define a column or columns, Contextures has a great addition to the code provided here that allows you to do that.
    http://www.contextures.com/excel-data-validation-multiple.html#column[/quote]

    Code works fine. However, I can't seem to deselect an item. When I want to remove an item from the selection, it's just not removed. Does anybody else experience this problem too?[/quote]

    Hi all,

    Any solutions found for this problem..please share..
  • To post as a guest, your comment is unpublished.
    CynthiaB · 4 years ago
    @Mervyn [quote name="Mervyn"]@Cynthia,

    If still required, you should be able to do something like this to only ensure the code runs on specific columns, in my case, column 34 and 35:

    If (Target.Column 34 And Target.Column 35) Then Exit Sub

    'Put this code at the beginning after your dim statements[/quote]


    Hi @Mervyn,

    Lost track of the thread completely, but thank you so much for your responses.

    I've tried applying the
    If (Target.Column 34 And Target.Column 35) Then Exit Sub
    (my version reads If (Target.Column4 And Target.Column5) Then Exit Sub
    as you supplied, but am getting a "Run-time error '438': Object doesn't support this property or method"" error on this new line.

    Here are the first few lines of my code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    If (Target.Column4 And Target.Column5) Then Exit Sub
    If Target.Count > 1 Then Exit Sub

    On Error Resume Next


    My worksheet only has 6 columns: Question | Answer | Category | Sub-Category | Tags | Photo link
    I only need multiple value drop downs in Sub-Category and Tags (columns 4 & 5).

    I'll keep looking for info as you suggested on 12/23, and will look at the link Charity provided.
  • To post as a guest, your comment is unpublished.
    THG · 4 years ago
    @Ramon Was there a response to this issue. It is the same issue I am having. There doesn't seem to be a way to remove an item that has been selected.
  • To post as a guest, your comment is unpublished.
    Ramon · 4 years ago
    Hi there,

    Code works fine. However, I can't seem to deselect an item. When I want to remove an item from the selection, it's just not removed. Does anybody else experience this problem too?
  • To post as a guest, your comment is unpublished.
    Charity · 4 years ago
    This works well, but I am unable to remove an item once selected. Any suggestions in case I click on something accidently and need to remove it without (hopefully) clearing the whole cell and starting over?

    Also, for those seeking to define a column or columns, Contextures has a great addition to the code provided here that allows you to do that.
    http://www.contextures.com/excel-data-validation-multiple.html#column
  • To post as a guest, your comment is unpublished.
    stef · 5 years ago
    Hi I am currently using this formula and all columns with data validation have the multiple selection option now, however I want to restrict the multiple selection only to one column. Can someone edit this formula for me so the multiple selection can be applied only to Column4? Thanks :)

    Private Sub Worksheet_Change(ByVal Target As Range)
    'Updated: 2016/4/12
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    If Target.Count > 1 Then Exit Sub
    On Error Resume Next
    Set xRng = Cells.SpecialCells(xlCellTypeAllValidation)
    If xRng Is Nothing Then Exit Sub
    Application.EnableEvents = False
    If Not Application.Intersect(Target, xRng) Is Nothing Then
    xValue2 = Target.Value
    Application.Undo
    xValue1 = Target.Value
    Target.Value = xValue2
    If xValue1 "" Then
    If xValue2 "" Then
    If xValue1 = xValue2 Or _
    InStr(1, xValue1, ", " & xValue2) Or _
    InStr(1, xValue1, xValue2 & ",") Then
    Target.Value = xValue1
    Else
    Target.Value = xValue1 & ", " & xValue2
    End If
    End If
    End If
    End If
    Application.EnableEvents = True
    End Sub

    Any assistance will be appreciated!
  • To post as a guest, your comment is unpublished.
    sunshine · 5 years ago
    @Yezdi Eks Hi Yezdi,
    Thank you for your comment. The code was updated and no duplicate values allow in the drop-down list now.

    Thanks.

    Sunshine
  • To post as a guest, your comment is unpublished.
    Mervyn · 5 years ago
    @Cynthia,

    If still required, you should be able to do something like this to only ensure the code runs on specific columns, in my case, column 34 and 35:

    If (Target.Column 34 And Target.Column 35) Then Exit Sub

    'Put this code at the beginning after your dim statements
  • To post as a guest, your comment is unpublished.
    Mervyn · 5 years ago
    Hi Cynthia,

    If the original author doesn't reply, I'll get you an answer but I'll only be in front of a computer on 29 Dec again. I'm also no VBA programmer. What you can do in the mean time is Google search how to identify the column number and only let the code run if data is edited in that specific column(s). I've done it but the code is on my work PC and can't recall it at the moment,maybe try putting a debug.print on target.column or something to that effect to see if it gives you the column number being edited.

    Sorry Jennifer, not sure about the issue you're having :(
  • To post as a guest, your comment is unpublished.
    Jennifer L Price · 5 years ago
    I was able to get the code to work, but then when I saved the document (with macros-enabled), closed it and returned, the code didn't work anymore (though it was still in there). I can't figure out what I've done wrong. Any ideas?
  • To post as a guest, your comment is unpublished.
    CynthiaB · 5 years ago
    Hi. Thank you for the code and the addition to limit duplicates.


    One more request - what addition/change would have to be made in order to allow multiple selection in only one or two specific columns? This code is re-adding lines of text to what should be 'plain' cells if I go to correct a typo, or make a change or addition to the text in the cell, as opposed to just behaving 'normally' and accepting the change (without re-adding the entire text again).

    For instance, column A is a 'plain' column. I write a sentence "What are the three itmes you want most?" Column B is a 'list' column where I only want to be able to pick one single value (in this case, let's say a child's name). Column C is another 'list' column where the user must be able to select multiple items (which this code allows me to do perfectly).

    As I go along, I realize that I've made a typo in column A and want to correct it. As this code stands, if I go in (double click, F2) and make the correction to the word "items", I end up with this result in my cell:"What are the three itmes you want most? What are the three items you want most?"

    thank you in advance for any help (from a user that REALLY likes VBA, but is still at the very earliest stages of learning!)
  • To post as a guest, your comment is unpublished.
    Mervyn · 5 years ago
    Just realised I didn't exit the loop in the new function if the condition has been set so we don't have to check other entries.
  • To post as a guest, your comment is unpublished.
    Mervyn · 5 years ago
    You can change the code in the following lines to prevent the duplicates:
    If xValue2 "" Then
    Target.Value = xValue1 & ", " & xValue2
    End If

    To:
    If xValue2 "" Then
    If CheckIfAlreadyAdded(xValue1, xValue2) = False Then
    Target.Value = xValue1 & ", " & xValue2
    Else
    Target.Value = xValue1
    End If
    End If

    And then add the following function:
    Private Function CheckIfAlreadyAdded(ByVal sText As String, sNewValue As String) As Boolean

    CheckIfAlreadyAdded = False

    Dim WrdArray() As String
    WrdArray() = Split(sText, ",")

    For i = LBound(WrdArray) To UBound(WrdArray)
    If Trim(WrdArray(i)) = Trim(sNewValue) Then CheckIfAlreadyAdded = True
    Next i

    End Function

    --
    There's probably better ways of coding it but it works for now.
  • To post as a guest, your comment is unpublished.
    MichaelB · 5 years ago
    It is great that this allows multiple selections but like @Yezdi commented, I am finding it will add one or several duplicates even if I don't choose them.

    So, at present, this is an 80% solution... one tweak away from perfect. I am not a VB coder or I'd offer the solution.
  • To post as a guest, your comment is unpublished.
    Yezdi Eks · 5 years ago
    Hi,

    Thanks for the solution and the code.

    But the next step is how to make sure that the user
    does not select "duplicate" values from the dropdown list.

    E.g. If there are 4 items in the list -
    orange, apple, banana, peach

    and if the user has already selected "orange", then excel
    should not allow the user to select "orange" OR that option
    should be removed from the remainder of the list.

    Can you please publish the code to accomplish this feature.

    Thanks.

    Yezdi