Microsoft Office Tips:
-Aviral Mittal (avimit {at} yhaoo {dat} com)
Connect @ https://www.linkedin.com/in/avimit/
Comments welcome on:


 Excel drop-down Menus are very common, but the problem with them:
User can only select 1 item out of the drop-down list.

Let us see how to device a Multiselect drop-down Menu in MS-Excel. This technique is implmented
using what is called a ListBox in Excel. This article can also be used as a tutorial on how to
make ListBox in Excel.

This technique will help the user to have a drop down Menu, with multi-select possibility.
The image below makes it more clear




Exciting!
Lets see how can we do it.
Note: It requires some knowledge of Visual Basic, as this solution
is developed using VB.

Steps:
1. Open a new Excel file and save it as macro-enabled Excel File
File -> Save As -> Excel Macro-Enabled Workbook (*.xlsm)




This is required because the solution is developed in VisualBasic as a Macro.

2. On the Green Excel Tab Developer -> Visual Basic



You can see Visual Basic at the top Left side. When you will click on it the Developer Environment will open.



3. Click on VBAProject (Book...), Right click -> Insert -> UserForm
You will see this:



You have just created what is called a User Form. This form will help to get inputs from
the User Excel File:

The next step is to add a ListBox onto the above created UserForm.
For this you ahve to open the Toolsbox Window (If not already open). This can be done
by clicking on the icon shown below:



The Toolbox window looks like this:



From the ToolBox window, select the ListBox Icon as shown below, and drag this list box on to your UserForm






It will look something like this:



Adjust the size of this ListBox as per your liking.

Now Add what are called the Command Buttons. These are also available on the ToolBox Window.
Select the command button and drag it to your UserForm







Rename the CommandButton1 to 'Cancel' and also adjust its size. You can do this by
Selecting the button first, then clicking inside it after a 1-2 gap.
If you double click it(i.e. your gap isn't enough), it will open VB editor. Close it and try again.

Instantiate 3 more command buttons and name them Clear, ALL, OK, adjust sizes of
Command Buttons,
ListBox
UserForm so that it looks something like This:




Let us now adjust the properties of the ListBox as per our requirements.
Select The List Box, you will see the Properties changing. It shd say ListBox



This Properties tab will show whatever you select in Right Hand Pane. e.g. ListBox, CommandButton, UserForm
Now Go down in the Properties table and change the value of "ListSTyle to" "1 - fmListStyleOption"



Similarly go further down and change the value of "MultiSelect" to "1 -fmMultiSelectMulti"

OK, the GUI is done.

Time for coding.

Go to the VBAProject Pane on Left Top Corner, Selet your Sheet1(Sheet1), and doubble click on it.
VB Editor will open
Cut+Paste following code:
-------------------------------------------------
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
-----------------------------------------------------------------------------------------------------




Now, Insert a Module just as you inserted UserForm Above
VBAProject(Book4.xlsm) -> Right Click -> Insert -> Module

Another VB Editor will open with heading Book....xlsm - Module1 (Code)

Cut+Paste the follwing code in here:
--------------
Sub enEvents()
  Application.EnableEvents = True
End Sub
---------------------------------------------------


The Subroutine
Worksheet_SelectionChange
which we added earlier will enable you to pop your newly built UserForm1 as the user clicks anywehre in the excel sheet Column 1.

So let us try it:

Go to your Excel Sheet. And click anywhere in Column 1. You should see something like this:



If it does not happen, try the following

Go to Excel Book Green Tab -> Developer -> Macros
You will see a window pop up showing enEvents, click on Run



Then try clicking on your column 1 again in your work sheet. Your Form Window should now pop-up.

Hey! you have just created a pop-up window. (But it wont do anything, even if you click on your buttons, nothing will happen)

Now we need to put the data in this form, as per the User-Inputs.

Before we start with this form again, we need to first create a data validation list, and then apply data validation to Column1.

For this I would create another Sheet in the workbook, and store the list in this new sheet. Let us call this sheet as "Lists".




Click on the + sign as show below, a new sheet will appear as "Sheet2" click on this name to re-name it to "Lists"



Let us now assume that we are making a list of selectable Countries, and we need to do multiple
selections from this list of multiple countries.

Enter the name of countries in the Sheet "Lists", column 1




Then Select this list, i.e from Row 1 to Row 9 in example shown above, and give it a name.

Select -> Right Click -> Define Name ->

 

Fill in the name as "Countries" and click OK

Go back to Sheet1, then Select First Column , you may see your window pop-up, if it does,
just kill it by clicking the X on top right hand corner.
After selecting the First Column
Excel Green Tab -> Data -> Data Validation Icon -> Data Validation ...




A form will pop up, fill in the following as shown:




You need to select "List" just below "Allow:"
then just below the "Source"
Type '=Countries' as shown above and click OK, Make sure to spell the list right, or it wont work.

This is basically making your Column 1 (A), as a single select list of countries.

The objective now is to be able to change it to multi select list.


Right Click on Module 1 -> View Code:

Add the following to the code of Module 1, so that it now looks like This:

------------------------
Option Explicit
Global gCountryListArr As Variant
Global gCellCurrVal As String

Sub enEvents()
  Application.EnableEvents = True
End Sub
---------------------------------------------------------------------------










Right Click on Sheet1 (Sheet1) -> View Code:
Add the following to th3 code, so that it now looks like this:

----------------
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
-----------------------------------------------------------------------------------















Right Click on UserForm1 -> View Code

Add the following code the code , so that it now looks like this:


------------------------------------------------------------------------------



Done!. Now click on your Column A of Shee1, do mulitple selection, and you will see what you wanted!!!
Download Excel WB with Code

Well someone has anonymously communicated a message to me, that I have deliberatly cut some code
to Force download.
Well, i am offering a free solution, and there are sites who are selling it. So I think I deserve to know at least
who is downloading or rather how many downloads. I am really not sure why someone will be offended by it.
I have tried to make it very simple for Net Users, and I can see several downloads with thanks messages.

Next Tip ->

KeyWords:
Excel Multi Select Pop-Up.
Excel Multi Select Drop-Down List
Excel Multi Select Drop Down Menu
Excel Multi Select Drop Down Menu with Checkboxes
Excel List Box Tutorial
Excel How to make a List Box