Assigning information to a specific place based on drop down box selection

**fengzi** · 04-16-2010, 11:05 PM

One of my worksheets is an index of information about foods, and the foods are grouped into categories like fats, dairy, vegetables etc. In order for people to enter new foods in to the index, I have created an area at the top of the worksheet where they can select the category from a drop down box then enter the rest of the information about the food (e.g. energy, protein content etc). Then they should be able to click a button and the information will be placed in the correct position in the index, based on the category they chose.

Initially I wanted to use the find function to copy the name of the category chosen and paste it into the find dialogue, but this can't be done using record macros. I checked how to do it but it seems you need to enable microsoft object forms 2.0 in VBA, but I need a solution that is more simple than this so anyone can use the spreadsheet. I thought perhaps there is another way around it apart from using the find function?

Thanks!

**Palmetto** · 04-17-2010, 07:40 AM

I need a solution that is more simple than this so anyone can use the spreadsheet. I thought perhaps there is another way around it apart from using the find function?

The solution can be as simple as pressing a button which has a macro assigned to it. In order to get a precise answer to your need upload a sample workbook that meets the following criteria:

1. It EXACTLY duplicates the structure in your real workbook, AND
2. It contains representative but non-sensitive data, AND
3. It shows an example or two of the desired results

**fengzi** · 04-17-2010, 09:48 PM

Ok, please see attached.

**sujitshukla** · 04-18-2010, 03:27 PM

Originally Posted by fengzi

Ok, please see attached.

Can you attach the file in 2003 format please.

Sujit

**Palmetto** · 04-19-2010, 07:35 AM

This works with the sample workbook you provide (drop down down doesn't work because of external links). See attached.

Please Login or Register  to view this content.

@ sujitshukla

Can you attach the file in 2003 format please.

Download and install the Office 2007 Conversion Pack and you can open 2007 version files (though 2007 Pivot tables will be disabled. Search Microsoft for the coversion pack.

**fengzi** · 04-20-2010, 04:49 AM

Thanks Palmetto, that is exactly what I was looking for.

However, when I try it in my workbook I get a compile error and it highlights 'cells' in the following code:

lrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

I have kept everything exactly the same as your worksheet, I don't understand what the problem is. The only difference is I have other macros and worksheets in my workbook.

**Palmetto** · 04-20-2010, 07:08 AM

I have kept everything exactly the same as your worksheet, I don't understand what the problem is. The only difference is I have other macros and worksheets in my workbook.

Two possibilities I see are:

1. You don't have a Sheet1 (in my code Sheet1 refers to the sheet code name not the worksheet name)

2. The Dim lrow part of the code is missing rendering the line of code as having an undeclared variable . This is unlikely if you used the code as given.