Multiselect Listbox is a very handy tool which allows users to make multiple selections from a complete list. It can be very useful in creating Interactive Dashboards and Interactive Charts
HOW TO CREATE & USE MULTISELECT LISTBOX:
Step#1: Get the complete list of items inside the List box
Activate “DEVELOPER” tab > Go To Insert> Go to Form control> Select List Box
Right click “List Box” > Click “Format control” and make selections as show below.
Step#2: Extract the selected items in List Box to Excel
“Cell Link” does not work when you choose the selection type as “Multi” or “Extended”. Some VBA coding is required to extract the items selected into Excel
Following is the code that will extract selected fruits list into Excel. Click here to Download the file, look at the code and play with it. You can copy the code and modify it to suit your needs.
Step#3: Dynamically link the chart to selected items
Now, we need to write a code so that the Chart’s data source shrinks and expands whenever you select and deselect items in the list box.
To achieve this we need to reset the Chart Data Source every time there is change in LISTBOX.
Sub adjustchartdata(ByVal count As Integer)
Dim mychart As Chart, myrange As Range
Set mychart = ActiveSheet.ChartObjects(“Chart 1”).Chart
Set myrange = Range(“B4:H4”).Offset(count)
mychart.SetSourceData Source:=Range(Range(“B4:H4”), myrange)
End Sub