Data Validation to Restrict User Input

OK... will have a look over weekend

roy UK & annaeye check your U2Us

Hi

This replaces copy 001.

Added a little macro to clear the invoice detaiks after saving

Annaeye,

Try this file

Roy : I tidied up your code :wink1:

Regards & :cheers:

Hi Will

That seems to do the job.

Just one small thing, I think the Suppliers tab needs changing to Customer

Have a good weekend

Roy

OK...

Here's my final answer..

Unless someone comes up with something in the meantime, I'll take a look tonight....

Got a bit of a grizzly boss in today so i'm being worked a bit too much! :spin:

In the meantime, try looking at how the other drop down list (for suppliers) was created using named ranges & data validation & have a go at doing your "Artist" one that way.

Also, can you post the email part of the question in the Email forum... as a separate issue, that way you'll possibly get an answer quicker.

HTH

Added a column for artist in the summary and amended the code to save this as well.
Emails Invoice only as a new Workbook. I will have a look at removing the code from the new book and also the buttons.
The only problem that I can see with emailing the workbook is that it can be altered at the other end. Maybe we can password protect the sheet, but this would not be foolproof

Sub Email()
'
' Macro1 Macro
' Macro recorded 17/03/2003 by Roy Cox
'
Dim SBookName
ActiveSheet.Copy
Application.Dialogs(xlDialogSendMail).Show
ActiveWorkbook.Close SaveChanges:=False
'
End Sub

This is the email code attached to a button on the Invoice sheet.

This is the line of code added to the bottom of the save macro

Sheets(DATABASE_SHEET).Range("H" & COUNT_ROW).Value = Range("Artist").Value

Name H14 to Artist

Add a column in the summary and amend the lines referring to yes/no to "I" & "J"

This is the amended workbook

To look at the code right click on the relevant button and choose "Assign Macro" then edit.

Or go to Tools>Macros. select the macro and then click Edit

The drop downs are created without code, but using Data Validation. Search your help files for this, basically go to the Data menu and select Validation.In the Allow dropdown select List and then place your cursor in the other box (Source)and select your "list"
You can add a list on a separate sheet and use the insert name to call it something like "Artists" and then in the Source box type =Artists

The help file will give you more detailed info

By the way, if you want to play with the code always back up your work first. In this case you can downloasd the file again, but you won't always have that luxury

Roy:cheers:

Hello,

I am trying to do a similiar thing for a commissions system. This example is helpful, but I have a problem.

I want to pick up more than the cell immediately below the field. So if, going with this example, there was more than one entry on the form, I would like to take ALL the data (i.e a whole month's commisions) below the cells identified as fields into the excel database. Am I asking the impossible? Am I making any sense?

Is there a better way to do it (I have thought of one other rather nasty way but am sure it's doomed.) Any takers?

Thanks

MKNov

can you post an example ?

Roy

trying to attach rough draft of form

the above would be the form where the users enter the data (commissions on sales for a month / quarter).

I want this data (on save or button click?) to be exported to a tracker something like this...

Hi

I have had a quick look at your files. I would take a different approach. Data for comms could be entered individually using a simple form and posting the info to the Tracker using the code in the invoice example. I would then use a PivotTable to create a Report from the Tracker by Month and/or Sales person.
Let me know if this is an option and I will look at it tomorrow. The attached file outlines my suggestions

Roy