How to create a drop down box in Excel

Asked By Alan Goth 240 points N/A Posted on - 05/15/2011

Hi,

I have an Excel application that is just a simple crude database for my coffee shop. There is a form which takes order and prints the Receipt. We have 3 types of coffee in the shop and we are increasing. Currently, in the form we have to add the whole name “Black” , “Light”,  in the type field to create the receipt properly.

I want a drop down box there which will have all the types, so I can just select it from there. How can I do this? I am not an Excel expert, nor even a programmer. I hired a student to make this Excel file for me which I copy-paste every year.

I can see the code and that is very interesting! So I thought that maybe I can do this little tweak. It may be possible if you help!

• Status: Open
• Question Views: 1126
• Answer Count: 7
• Vote Up 24 Vote Down

• Answer Accepted: Yes
• Question Category: MS Excel

Best Answer by Simmy C-me

Go To Solution

Answered By Simmy C-me 0 points N/A #97213

How to create a drop down box in Excel

You are really brave I have to say, to play with the coding. If you really think that you are comfortable enough to do this tweak then I shall help you. But I don't know how your form looks. You have to explain it. Tell me: 1) What you do to bring that form. 2) What you do in that form. 3) How you print that generated script.

About Simmy C-me

Questions
0

Answers
16

Best Answers
6

• Vote Up 0 Vote Down

• Posted on - 05/15/2011
• Question Category: MS Excel

Answered By Alan Goth 240 points N/A #97214

How to create a drop down box in Excel

Thanks for the support. Ok here is your answer. I start the Excel file. It opens up. The first sheet has 3 buttons. 1 one of then is titled "issue receipt". I click that button and a form shows up. Having 3 fields:

• Name of buyer
• Item type
• Quantity

I fill all those fields and press the button “Generate”. It waits for a while, then a Print popup appears showing that print will start now. And after a while I get the print.

About Alan Goth

Questions
1

Answers
3

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/15/2011
• Question Category: MS Excel

Answered By Simmy C-me 0 points N/A #97215

How to create a drop down box in Excel

Ok.

• Now go to the developer mode- > view source.
• A new window shall appear, listing all the items used in the Excel software.
• In the left tree view you shall find a folder named "Forms". Expand it. You can see all the form's design in here; that are used by the application. Find the one you are using to issue receipt.

I shall continue when you are there.

About Simmy C-me

Questions
0

Answers
16

Best Answers
6

• Vote Up 0 Vote Down

• Posted on - 05/15/2011
• Question Category: MS Excel

Answered By Alan Goth 240 points N/A #97216

How to create a drop down box in Excel

Yes! I have the form design in front of me. There is the button and the three text fields. I can select them. Now what?

About Alan Goth

Questions
1

Answers
3

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/15/2011
• Question Category: MS Excel

Best Answer

Best Answer

Answered By Simmy C-me 0 points N/A #97217

How to create a drop down box in Excel

In the first part:

• Select the text field that holds the "Item Type".
• Go to view -> Properties window to show up the properties inspector.
• In that inspector note the value of the field (name).
• After doing so. Go back to the form design.
• If the toolbox is not showing around go to view>toolbox to see it.
• Delete the “Item Type” text field. And create a combo box in there.
• Now select the newly created combo box and rename it to the same name as the deleted text field (I told you to note out, it has to be exactly the same).

In the second part:

• Go to the home page of the Excel sheet. Make sure that you are in developer mode.
• Double click the "issue receipt", some code will show up. At the beginning of the code you should see something like:
• Private Sub CommandButton1_Click() or maybe
• Private Sub IssueReceiptButton_Click()
• Just after that line add these:

    <Formname>.<comboboxname>.AddItem ("Black")

Where:

Formname would be replaced by the Name of the Form that shows up by clicking this button.

Comboboxname is the name that you gave in the properties pane(formerly a text field).

After the add item inside the quotation add the items you want to see in the drop down list. Each on a separate line.

Like

    <Formname>.<comboboxname>.AddItem ("Black")

    <Formname>.<comboboxname>.AddItem ("Light")

    <Formname>.<comboboxname>.AddItem ("Ultra")

Hope this helps. If you find any trouble then just mail me the file. So that I can help much effectively.

About Simmy C-me

Questions
0

Answers
16

Best Answers
6

• Vote Up 0 Vote Down

• Posted on - 05/15/2011
• Question Category: MS Excel

Answered By Alan Goth 240 points N/A #97218

How to create a drop down box in Excel

Thanks very much. It was fun! I have got what I wanted and I think in the future I shall do lot more tweaks!

About Alan Goth

Questions
1

Answers
3

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/15/2011
• Question Category: MS Excel

Answered By Simmy C-me 0 points N/A #97219

How to create a drop down box in Excel

You are welcome.

About Simmy C-me

Questions
0

Answers
16

Best Answers
6

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: MS Excel