Multiple selection in drop-down menu with check box

[egeivic]

Hello Everyone!

I have 100 countries of which more than one participate in international deals. To save space: 2-digit country codes are also to be used.
If I click on any cell in a certain column (e.g. column "C" named "Participating Countries"), I would like to have a window pop up:

• with name "Select Participating Countries" in its Title Bar,
• with the list of the 100 countries,
• with their relating country codes, and
• check boxes before them.

After selecting the countries for a deal by ticking the boxes and pressing "OK" button (pressing OK automatically closes the pop-up window and appears again only when any cell is column "C" is selected), the cell I clicked in would list only the country codes of the countries selected (in that one cell, seperated by commas).
In Lotus Notes, this is so common and although I figured out one or two of the necessary VBA codes, I do not seem to be able to merged them into one operational macro.

Could anyone please help me with this?
Thanks in advance.

 

[DonkeyOte]

It would perhaps make more sense to use a ListBox in Multi Selection mode.

If for sake of example we assume:

a) the UserForm is called UserForm1

b) to the UserForm you have added a ListBox control (ListBox1) set to Multi Select with Column Count set to 2

c) to the UserForm you have added a CommandButton (CommandButton1) and set Caption to "Confirm"

d) your 100 country code listing can be found on A1:B100 on sheet "Data" - codes being in B

e) your selection sheet is called "Input"

then you could use the following code...

Right click on "Data" tab from native XL -> View Code -> enter below code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column <> 3 Or Target.Row = 1 Then Exit Sub
UserForm1.Show
Cancel = True
End Sub

To the UserForm1 object add the following code

Dim lngItem As Long
Const strSep = "," 'delimiter

Private Sub UserForm_Initialize()
With ListBox1
    .RowSource = "=Data!A1:B100"
    For lngItem = 0 To ListBox1.ListCount - 1
        .Selected(lngItem) = InStr(1, strSep & ActiveCell.Value & strSep, strSep & .List(lngItem, 1) & strSep)
    Next lngItem
End With
End Sub

Private Sub CommandButton1_Click()
Dim strItems As String
With ListBox1
    For lngItem = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(lngItem) Then
            strItems = strItems & strSep & ListBox1.List(lngItem, 1)
        End If
    Next lngItem
End With
With ActiveCell
    .NumberFormat = "@"
    .Value = Replace(strItems, strSep, "", 1, 1)
End With
Unload Me
End Sub

So in essence when you double click on any cell in Column C on Input sheet (other than header row) the UserForm will appear with your country list.

If prior selections have been made in said cell those selections will be selected by default.

Pressing "Confirm" will result in the current selection being written back to the cell - delimited as per strSep variable (in the above this is set as comma).

On a final note you can add Validation to the Col C on Input sheet such that a message is displayed to "Double Click to Select" (ie permit Any Value but setup an Input Mesage).

 

[DonkeyOte]

I just noticed a rather fundamental typo in the above and it's too late for me to edit now the below:

Right click on "Data" tab from native XL -> View Code -> enter below code

should of course read:

Right click on "Input" tab from native XL -> View Code -> enter below code

 

[egeivic]

Hello DonkeyOte!

You R awesome.. thanks very much for your help.

I'd have one more thing -> as you could see from my approach, I am kind of trying my wings only so this might be a stupid question: we are not using CheckBox because it can't handle list of several values, and we would need to create a box for each country but then there would be the problem of scrolling down through the boxes, right? Don't ask me why.. I am just hooked on checkboxes and would like to find out if it is even possible within Excel for multiple values.

Ciao

 

[DonkeyOte]

Change the ListStyle property of ListBox1 from Plain to Option.

Does that give you what you want ?

 

[egeivic]

I can't thank you enough.. though I can try

Muchas gracias!

 

[egeivic]

Dear DonkeyOte

I am unable to figure out on my own which part of your code is to be eliminated if - this time - I want to use the same, multi-selection function but with only 1 row column in our Data tab (e.g. A1:A100). It has to do with lngItem - that much I figured but what to change exactly.. I do not seem to be able to make it work.

Could you please help? Thanks in advance.

 

[DonkeyOte]

First you would alter the Row Source for the ListBox to utilise the vector of interest, ie:

.RowSource = "=Data!A1:A100"

Then given the ListBox contains only one column all references to lngItem should now be using Column 0 rather than 1 as is presently the case:

List(lngItem, 0)

(or omit 2nd parameter altogether of course)

 

[avimit]

I have solved your problem. I have used Listboxes to develop this solution and it works perfectly!
Its uploaded on my web site:
http://www.vlsiip.com/exceltips.html
'enjoy

Hello Everyone!

I have 100 countries of which more than one participate in international deals. To save space: 2-digit country codes are also to be used.
If I click on any cell in a certain column (e.g. column "C" named "Participating Countries"), I would like to have a window pop up:

• with name "Select Participating Countries" in its Title Bar,
• with the list of the 100 countries,
• with their relating country codes, and
• check boxes before them.

After selecting the countries for a deal by ticking the boxes and pressing "OK" button (pressing OK automatically closes the pop-up window and appears again only when any cell is column "C" is selected), the cell I clicked in would list only the country codes of the countries selected (in that one cell, seperated by commas).
In Lotus Notes, this is so common and although I figured out one or two of the necessary VBA codes, I do not seem to be able to merged them into one operational macro.

Could anyone please help me with this?
Thanks in advance.