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!
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.
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.
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.
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?
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.
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!
