Tip sumitted by:
Ben Sullins, Phoenix, AZ
Using a visual basic combo/edit box is a somewhat advanced concept that,
as you will find, can be very useful in many ways.
First we need to have a list of values that are going to populate the combo box.
In this case I’m going to use the list of names from a previous tip.
Once we have the list were going to name it using ‘named ranges’.
To do this we simply highlight the range of data and then click in the cell
locator box and type in the name we want to use.
Next we need our data that is going to be used in the display of the report.
I went to http://www.nba.com/suns/stats and just copied the data into
the ‘data’ sheet.
Now that we have our data lets create a named range to be used in the
lookup on the display page.
Now we need to create a place for the selected player name to be held.
You can do this anywhere, but in this case I’ll have it be displayed
on the main page of the report. To do this I create another named range
called ‘playerlnk’.
Since we’ve used named ranges we can make our formula the exact same for
all the data that is to be displayed on the main page. Here it goes…
=VLOOKUP(playerlnk,playerlk,MATCH(C5,data!$A$3:$P$3,0),0)
At first this will display an N/A error because we do not have any valid
value in the ‘playerlnk’ field. So from here we can throw in some error
checking to the formula.
=IF(playerlnk="","",IF(ISNA(VLOOKUP(playerlnk,playerlk,
MATCH(C5,data!$A$3:$P$3,0),0)),,VLOOKUP(playerlnk,playerlk,MATCH(C5,data!$A$3:$P$3,0),0)))
Its time to create our combo box…
To create a visual basic combo/edit box first
select view>toolbars>visual basic,
the visual basic toolbar will appear. From the visual basic toolbar click on
the ‘Control Toolbox’. It’s the hammer and wrench icon.
In the control toolbox you will see a Combo Box item, click on it,
then click on the spreadsheet where you want the box to appear.
Now that we have our combo box we need to fill it w/ the player names.
To do this, right click on it, select properties, then in the ‘list fill range’
enter the named range we want to use to fill the combo box with.
Were going to use ‘namelst’ in this situation. Next we need to tell Excel
where to put the selected value, again we use a named range. Enter ‘playerlnk’
in the property ‘linked cell’
of our combo box.
At this point you need to close the properties box, and exit design mode.
To exit design mode click the ‘Design Mode’ button in the ‘Control Toolbox’.
It’s the triangle ruler and pencil icon that is light blue. We can now test
our report by selecting a name from the list and watching it fill the cell.
Then the formula should bring back the data from the other page.
To make this report more user friendly, lets hide the ‘ref’ and ‘data’ sheets so
our users don’t get confused. You can do this by selecting the ‘ref’ sheet,
holding control, and selecting the ‘data’ sheet, then going to format>sheet>hide.
I have left them unhidden in the attachment.
In this way we can create vb list box in excel.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
loans bad credit loans guaranteed how does debt consolidation work online payday loan direct lenders