Dropdown list with multiple selection in cell

Copper Contributor

I have a item list in sheet 1 of work book, A2 to A47 I would be adding more items to this list. On sheet 2 I have 7 columns A to G which I will have A (name), B (surname), C (mobile), D (email), E (Country), F (industry), G (status). I would like to have a dropdown list in F to add multiple in a cell as I add information in row what is VBA code i can use to have multiple item entries in F2

20 Replies

@Charlie_The_Connector 

You can use VBA code to create a dropdown list with multiple selections in a cell. Here is an example of VBA code that you can use to achieve this:

 

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 = "$F$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

 

This code will allow you to select multiple items from the dropdown list in cell F2 by separating them with a comma. You can modify the code to fit your specific needs.

After also creating a dropdown menu, you can use this code to select multiple items from the dropdown list in cell F2 by separating them with a comma. You can adapt the code to your specific needs.

Attached is an example file where you can test the function.

 

Hope it will help you in your project!

Hi @NikolinoDE

Thank you so much for your assistance...

I am not conversant with what code is and don't have the understanding ...

I was successful with the testing though when I named sheets did not work...

After inserting rows above to insert picture and freeze panes for "Headings" Sheet 1 is renamed to members ... data entry rows start at G9 and sheet (1) name is Members ....

 

So in essence Sheet 1 "Members" entry of dropdown would be from G9 to G108

Sheet 2 is renamed to Industry...

 

Also as multiple entries will be in G9 is there a way to view as a window if possible please

 

Regards,

 

@Charlie_The_Connetcor 

@peiyezhu

Thank you Sir appreciated, though in Chinese and could not figure out instructions

@Charlie_The_Connector 

In order to accomplish this, at least basic knowledge of VBA is required.

Here is another example,

...but in order to get a more precise solution, I recommend that you insert a file (without sensitive data) and explain step by step what exactly you want to do.

In addition, I recommend the Excel version, operating system, storage medium, etc. to inform.

All of this would make things a whole lot easier for everyone.

 

 

@NikolinoDE,

Thank you for your assistance and patience ...

I am learning how to do this and coding is way out of my league LOL

Please find example file as requested and what I would like to achieve

https://docs.google.com/spreadsheets/d/1bEu2Hz-Ir4gKE6qzIsIYS1oge0yl9TOU/edit?usp=share_link&ouid=11...
I could not figure out how to add .xls file

I am using a Office 2016 ...

Regards,

Charlie_The_Connector
Ok.
My Edge browser can translate foreign language to native language。
If possible,try Chrome or Edge web browser which may solve translation problem.
Additionally,I can not download your file from google.com because of network reason.
if you can Open full text editor to upload file here,more people may have chances to get what exact you want。

@peiyezhu 

 

Thank you for your response ...

 

I do not find a way to insert file ...

Spoiler
 

upload file to forum。

https://v.douyin.com/DmSMBYB/ 

 

please refer to above video。

 

 

Screenshot_2023-04-27-13-23-37-400_com.example.xch.scanzxing.jpg

@Charlie_The_Connector 

@peiyezhu 

 

functions not available on PC

what did you mean functions not available on PC?
upload file to the forum not available on PC?

@peiyezhu 

 

Yes, I have search for solution and only way I could do that is via google drive ...

Also video you sent is in Chinese and I could not figure out instructions ... those functions are not on PC ...

@peiyezhu 

 

Thank you I have figured out ...

 

See attached ...

https://learn.microsoft.com/en-us/office/vba/library-reference/concepts/getting-started-with-vba-in-...

After I downloaded your attachment,I found it is a .xlsx file without macro.

If you want to develop your customerized input control,I am afraid you have to know something about VBA Programming firstly.

So I post the link for your reference.

Additionally,Do you want to enter multiple industries in one cell of column G of sheets("Members") for any member ?

like
G9 =Agriculture
Government

@peiyezhu 

 

That's correct, it is only cells in G that will need multiple entries...

@peiyezhu 

 

I'm actually doing any code, have no clue what it means and don't have the accumen, hence me seeking help to do correctly ...

 

Thank you for you patience and assistance

@Charlie_The_Connector 

 

I have uploaded the sample file for your reference.

As I have on windows PC on hand,I can not debug VBA for your excel workbook.

If any question you meet during development,we can discuss here.

@peiyezhu 

 

I could not make head or tail from the file you sent...

Thank you for your assistance ...

 

I will have to find someone local who can do this for me...