control to Excel worksheet makes data entry easier and interesting:
Let's say we take a loan against purchase of our dream car. We have
different models to choose from and these models have different prices.
We would use the 'pmt' function to calculate our monthly payments. In
the 'PMT' function we need to enter the following parameters: Loan
Amount, Period of loan and Interest Rate on the loan. In our case we
wish to make the data entry of the model and immediately get the
related price in an appropriate
cell of the Excel worksheet, preferrably next to the loan amount. We
achieve this as follows with the combo box control:
Type the list of the models under the header 'Car
Type the related prices under the header 'Price'
We select a cell in our table and press Ctrl+Shift+*
select the whole table
On the Insert menu select Name, Create. Select Top
check box, clear any other checked boxes and click OK. This action
creates two named ranges: 'Car_Model' for the list of cars and 'Price'
the related prices
From the view menu, select Toolbars and display
Click the combo box button on the forms toolbar
Go to an appropriate cell and drag and draw a combo
Right-click the combo box, select 'format control...'
display a dialog box
In the input range type 'Car_Model', in the cell link
appropriate cell address D2 or D3 and then click OK
Press Esc to deselect the combo box
Click the arrow of the combo box to see a list of car
models. Select a model of your choice. You will see a number displayed
in the 'cell link'
The combo box button has been linked to the car list.
you need to type a simple formula in cell, let's say, 'C3' to retrieve
the price from the named list range 'Price'. The formula you apply is
The Excel training video demonstrates the concept in