Developing a food costs sheet, couple of issues

**garyi** · 08-31-2010, 09:33 AM

Hello all. This is my first post so please be gentle. I have posted in the new section as I should imagine these are basic queries.

In essence I have with helped created a food costing sheet, I work in the catering industry. My aim is to make it as simple as possible to use (for other people). but the nature of my business dictates that we cannot give everyone a copy of Access but in general every one has Excel on their computer.

The sheet is based on Vlookup with a seperate page containing the products, with cost/weight next to each item.

On another sheet the user has a pop up box for each line of the ingredients lists which taps via data validation that product page. Once they have chosen something the vLookup pops the weight and cost in the ingredients line.

The trouble for me is I am now up to and beyond 300 items in the products page. The pop up list in data validation has got unweildy and has a number of REF! errors, and blank lines that do not relate to anything. My concern is data valiadation lists were not necesserily designed for 300+ entries?

So is there another way to do this?

One other issue is in VBA, I have a button on that ingredients page that when clicked renames the worksheet tab to the content of a specific cell (i.e. the dish name) however if the user enters the name of a tab already with that name it bumps out to debug. I am hoping there is a better way, i.e. if they give it a duplicate name a warning will pop up. If someone could just point me in the right direction that would be great.

Many thanks

**Richard Buttrey** · 08-31-2010, 09:56 AM

Hi Gary,

I know of no reason why data validation should not work with 300+ entries. Presumably you're getting #REF errors because the underlying list has errors and you'll need to sort that out first. You could if you prefer use a Userform with a ListBox allowing the user to select an item but this is essentially the same functionality as the Data Validation in Excel.

As far as the duplicate sheet name is concerned you could use code like the following.

Please Login or Register  to view this content.

**garyi** · 08-31-2010, 10:01 AM

Hi richard. Would you know why this problems occur? For instance in the products page there is an entry for olive oil followed by olive oil small bottle.

Back in the Drop down list in between them two items is three REF! lines

Also there is four blank lines even though I ticked the 'ignore' blanks box.

Thankyou for taking the time with the VBA code. I feel like someone in a dark tunnel who can see the light, its just a heck of a learning curve!

**garyi** · 08-31-2010, 10:17 AM

The VBA code worked a treat, many thanks

**garyi** · 08-31-2010, 10:30 AM

Sorry to bother you again the VBA code seems to be telling me that the sheet name already exists regardless of what I name it. I swear it worked the first time.

Here is the code: my input was not a lot!

Sub myTabName()
On Error GoTo ErrTrap
ActiveSheet.Name = Range("B6")
On Error GoTo 0
ActiveSheet.Name = ActiveSheet.Range("B6")
Exit Sub
ErrTrap:
MsgBox "The sheet name " & Range("B6") & " aready exists"
End Sub

Sorry to be a pain, I am nearly there! I am looking round the forum for answers, but VBA does seem to offer 1000 ways to do the same thing, confusing tot he learner.