Free Microsoft Excel 2013 Quick Reference

Excel 07 Using Macros - Taking User Input to populate drop down list


I am new to VBA and hoped to get some suggestions on how to automate a regular task that I'm assigned.

I will try to describe the total scope of what I'm trying to accomplish and hope to give you enough information so you understand where I'm going enough to give some input.

I want to create an Excel template file that takes another Excel file as the data source and creates a printable report with summarized data and graphs based on some user input. This is the initial step, after I get to this point, who knows what'll be next!!

The data source is an Excel file that is created from another analytic program and exported in a standard format (approximately 16 tabs of results, with each of the tabs containing the same columns just summarized at the tab level. The tabs are named like: overall, type product, region, etc... There are about 20 columns under each tab and they look like: [tab name], overall, type, product, region, etc...)

What I envision is that the user would input the location of the folder that contains the source Excel files to be used, this could change each year so I don't want to hard code it. From this information possibly a Macro would read the contents of the folder and create a drop down list of all Excel file names in that folder. The user would then choose the file name from the drop down list and a second drop down list would be created that contains the list of all the tab names. The user would choose the tab (or summarization) that the report would be based. Then the report and any charts would be auto populated. None of these drop down lists or input boxes need to incorporated if there is a better way.

I am not sure what the best approach is to accomplish this but any suggestions and code examples would be greatly appreciated.

Thanks in advance,


Post your answer or comment

comments powered by Disqus
In Sheet1 I have the following table:

A1: Product ID B1: Description
A2: 1101 B2: Eggs
A3: 4367 B3: Oranges
A4: 5433 B4: Cereals
A5: 7789 B5: Rags

In sheet 2, cells in column A will contain a drop-down list where the user can choose a product. However, the list items should look like this:

1101 (Eggs)
4367 (Oranges)
5433 (Cereals)
7789 (Rags)

Is this possible with just an Excel formula or do I need a macro? I used to make drop-down lists by giving names to ranges but that only works when my list items need info only from a single column.

I need to asign macros to a drop down list, but whatever i do I cannot find a way to do it?
Can any one point me in the right direction?
I am working with a spanish version of excel and my spanish is not good enough for the help menu!!
So I´m hoping that someone can help me here

How do you let a user add items to a drop down list bown that was created by validation, if the answer they are looking for is not there. I also need it to automatically add that entry to the list box.

How do I tie prices to a drop down list of products in excel? I have a column
of products that I turned into a drop down box and I have a column of prices
that I want to fill in when I select an item from the drop down list.

When I try to add a name to the drop down list it says a user has restricted
something something sorry don't remember.

Hey everyone,

This may just be a quick fix that I don't know about; nevertheless, I am trying to put together a project and in order to allow for maximum customization. I want the user to be able to expand a drop-down list by adding any additional fields he/she may deem necessary for his/her purposes. I know the standard way of creating a drop-down (define name, validate, and boom! you have a drop-down) but the unfortunate thing about this method (as is well known) is if I try to define the original list name and include some blank spaces (in anticipation of the future additions which will be added by the users) all of the blank spaces are also included in the drop-down. So the question is: is there a way to allow for future additions to a drop-down list while avoiding creating a drop-down with five fields and 10-100 blank spaces? Any help would be greatly appreciated


Hi there,

I was hoping for a little help. I need to create an invoice in Excel. As the invoices will always contain variations of the same list of products, I would like to have drop-down lists whereby I can click on a cell & scroll down to the relevant product rather than having to type the same or lists every time. I have seen this somewhere before in Excel but can not figure how to do it.

Also, is it possible to create a master invoice which when saved will save a copy with sequential invoice numbers?

Many thanks in advance.


How to create Drop-down list with both (numarical & text) in a worksheet?

Whenever I tried it with entering data and then right click the mouse button and choose 'CREATE LIST' ---- its creating but also showing entire data on worksheet.

The requirement is-- whenever user click on "Choose from the list" cell; there automatically a drop-down list will appear on the worksheet & user can choose their rerquired field.

how could one make it possible??

I was able to create the link pictures to a drop down list following the Ron's instructions

Select Sheet2 and turn off Grid Lines
...<Tools>Options><View tab>Uncheck: Grid Lines

1)For each picture to be displayed:
1a. <Insert><Picture from file> (select picture and put it in the sheet).
1b. Select the range of cells that contains the picture.
1c. Name that range of cells, using the prefix "pic" followed by the dropdown list text:
Example for a picture of an Elephant:
Names in Workbook: picElephant

2)Build your data validation list on a cell in Sheet1 and pick one of the items.

3)Create a dynamic range name that refers to that cell:
Names in Workbook: ShowMyPic
RefersTo: =INDIRECT("pic"&Sheet1!$A$1)
...or whatever cell you chose.

4)Copy/Paste one of the pictures from Sheet2 to the display cell on Sheet1.

5)With the picture selected, type this in the formula bar, then press [Enter]:

The picture will be replaced by the picture referred to by the dropdown list.

However, when I went back into the file to added new pic[name], the ShowMyPic does not bring in the new images.

The range names for all the new images match the originals so I don't understand why the new images are not captured. Is there an additional step required when modifying the original pictures in Sheet2?

Thank you

I have a work book with 10 range names, each representing blocks of cells. I am using a VLOOKUP formula and trying to use a drop down list of the 10 range names to populate the table array portion of the VLOOKUP formula. I keep getting an error. It appears that the data in the drop down list is just text and not the actual range of cells. Any sugestions would be appreciated. The VLOOKUP nworks fine when I type the range name directly into the formula.


Hello all!

I am new to the forum and this is my first post! Any help offered would be greatly appreciated. :-)

I work for a kitchen manufacturer. In order to build a kitchen, a detailer must work out all the different hardware components (screws, hinges, handles etc etc) that go into that kitchen and either order them or instruct the factory to take them from inventory when it is time to manufacture. At the moment this is all worked out manually and then purchase orders are handwritten (time consuming to say the least!).

So in order to speed up the process I have created an input sheet titled "cabinet order" with all the various screws and hinges etc based on which cabinet you choose which then autosums the totals into "Hardware Sheet" - this is simple enough and works fine. Then on the hardware sheet I have created a drop down list (sourced from another worksheet) for handle selection and accessories with the codes from a variety of suppliers. Depending on the selection from the drop down list, I then want this information to automatically transfer to a purchase order with the correct quantity requested. I have used "lookup" to do this. For example, there is 300 + handles, but on the purchase order i want it only to transfer what is selected on the drop down list if it is a particular supplier - so I have used this for the range which matches supplier one and the corresponding supplier code

=LOOKUP('HARWARE SHEET'!C13,'Handle Codes'!A1:A89,'Handle Codes'!B1:B89)

This works fine until a handle is selected not in the range because it is not that particular supplier and then the formula tries to find one anyway. Can someone tell me how to
a) stop it from looking for an answer when there isnt one
b) not show #N/A and
c) also get the user entered quantity amount to also transfer to the correct purchase order based on the handle selection???

Hope that long winded explanation all makes sense!

TA! :-)

I am making a macro enabled excel sheet. In this sheet I am creating a user form which contains a dropdown list. The problem is that the data that I want in the dropdown list is arranged in horizontal. I want the user to select the grade of concrete in the user form drop down list the source of which is saved in sheet 2 (C1:H1) I could do it in selecting grade of steel as the data was arranged in vertical. When I input rowsource as "sheet2!C1:H1, in the property box Combobox2, I can only see M15 getting selected. I dont get a drop down list.
Please let me know if requires any special coding.
Thank you.

Hey guys!

So my goal in this project is to create a userform that retrieves from the worksheet and also allows users to input data themselves from the userform and into an one column listbox on the userform. When the user is done entering in the data, the data is sent into the cell of their choosing (they enter in the row # themselves and it will always be column H), and creates a drop down list of the data they entered.

So far, I have created the userform and with retrieve, erase a line from the listbox, and a "complete& close" buttons. I also have the listbox and the textboxes made. The "complete & close" button is where i look to transfer the data from the listbox from the userform into the drop down list.

I'm not actually sure if this is possible, so I have been working my way around this problem. What I have now is that the listbox data transfers to a useless column on the worksheet in hopes to use that as the range for the drop-down lists. Unfortunately, I just realized that this would probably not work because the drop-down lists would change with the data in this column. Is there a way to make it so that once these drop-down lists are made, they no longer depend on the range and do not alter until the userform calls upon that particular cell again?

I know this is a complicated problem, but please ask for you guys to help! I'm pretty much a novice in VBA and I don't completely understand the limitations of Excel VBA. Help would be much appreciated for this problem that has been giving me headaches for days.


I am using INDIRECT to create a second dynamic drop down list (L1) that is based on the cell contents of a first drop down list (I1). I want to create a macro that will blank the 2nd drop down list ONLY when the choice in the first list is CHANGED. I have the following, but it blanks the second list as soon as the first list is clicked on, rather than when a change is made:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) 
    If Not Intersect(Range("I1"), Target) Is Nothing Then Range("L1").ClearContents 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
any advice appreciated, thanks, cheers.

Hi everyone,

I have a question. I would like to have a macro that when activated, gives a drop down list which contains all the tabs in the workbook. The user will select one and the macro will store this selection for later use. But I dont know how to make a drop down list with all the tabs, and make it possible to select one. How do i do this?

Thanks a lot

I am using Excel 2003 but have been a user of 2000 for a long time. I 've
worked through the creating of a drop down list and understand that but
cannot find advice on the following from Help, so could one of you kind
people out there explain to me:
How do I make changes to a drop down list (ie amend, add items). Indeed,
can I?
Can I use the list for all worksheets within a workbook (hence the need to
amend & add to the list?
I am cleansing a database which is why I am using the drop down list. I
shall then copy the workbook to disk to be loaded onto a completely different
pc (not connected in anyway to mine). Will the list be available if the
person that I am doing this for has another version of Excel?
Sorry to be dim.

When the (right-click) "Pick From Drop-Down List..." command is used in Excel
to facilitate key-entry, it only works for contiguous entries in cells in the
column above the input cell. blank cells in between break the link.

Is there a way to designate a named range of terms as the basis for the
"drop-down" list?

Microsoft,this should be a standard option!


I finally figured out how to get a marco to hide/unhide columns based on a drop down list and using an IF(or) statement in some cells (see this post: Link to other thread)

Here's the code for it:

'To hide columns with no data in row 2

Application.ScreenUpdating = False
With Sheets("Sheet1")
Dim col As Range
For Each col In .Range("E2:FH2").Columns
col.EntireColumn.Hidden = _
Application.Sum(col) = 0

End With
Application.ScreenUpdating = True
End Sub
So when the user selects a market from the drop down list and runs this macro, the macro only shows the market they chose. However this isn't done automatically.

Is there a way to make the macro run anytime there is something different selected in the drop down list?

If it helps, the list for the drop down has all, market 1, market 2, etc etc. So when the user selects market 1 the marco should run automatically and only show market 1. Then when they select all it would run automatically again and show all markets.

Currently the marco works as it should, just not automatically (i'm currently using a shortcut key).

Thanks for any help.

I have a spreadsheet used for estimating projects. There are categories
across the top (Electrical Engineering, Mechanical Engineering, etc.). Under
each category are job titles (anywhere from 1 to 9 titles for each category).
Each title has a dropdown list with names of people under that title. The
drop down list of names is converted to the person’s initials using an index

My problem comes when some of the columns (categories and/or titles) are not
used and are deleted or hidden. The formulas in some of the dropdown boxes
change – specifically, the input range and the cell link. I am using named
references, but Excel switches the reference in the input range to the name
that was used in the column to the left that was deleted or hidden. I want
the input range to stay the same. I also want the cell link to update to the
new location.

Can anyone help?

When you use the SUM function there are three ways of specifying the numbers
to add: you can type =sum(A2:B7), you can type =sum(1,2,3,4,...,n), or you
can use the formula editor. When a number is entered, another blank line
appears, making room for the next number.
I'd like to do the same thing with a drop-down list. How would I write a
macro that adds a drop-down list two cells below an existing drop-down list,
but the new list reflects the choices made in the previously existing list
(i.e. the same choice cannot be entered twice)?
Thanks in advance

When the (right-click) "Pick From Drop-Down List..." command is used in Excel
to facilitate key-entry, it only works for contiguous entries in cells in the
column above the input cell. blank cells in between break the link.

Is there a way to designate a named range of terms as the basis for the
"drop-down" list?

Microsoft,this should be a standard option!


i and making a food costing program for my restaurant

in my sheet i have a list of current prices in column M

and i would like a macro that will give me a drop down list ranging from 1% up to 20% as a choice for the user per line in column R

obiously i would need to insert a formula that would be (=M5* %) in R5 .?

but then how to make the macro once i have made the same formula from 1% up to 20 % into a drop down list in each row?

thank you

I have a Control sheet with a drop down list with numbers 1 through 8 and None available. I have also recorded some macros to be able to open sets of worksheets depending on the number chosen which all work. How can I link the selected drop down list number to the relevant macro?

Also can anyone explain why to hide worksheets you can use

but you cannot do the reverse?! i.e.


I have a lot of data that I built macros to sort when you when to see certain categories.

I had it so if you wanted to see Portfolio A, you click a button and it sorts the list of 100 names into the 25 that are in Portfolio A, and etc for other Portfolios.

However, now I have too many buttons and I would rather be able to use a Drop Down List.

I want a drop down list with 10 names on it, where for each name that I choose from the list, it runs a different macro. Is this possible?

All I can figure it is how to assign 1 macro to the drop down list, which seems useless to me. How can I make each name on the list assigned to a macro?

No luck finding an answer? You could always try Google.