Required fields and drop down boxes in Excel

[Epsilon101]

Hi,

I made an input database in Access to keep a log.
I am getting fed up of people not knowing how to use Access and i dont want to have to keep telling people how to use it correctly or teach someone how to change details.
I soon wont be dealing with this dbase, so i wanted to change it to work in Excel.

I wanted to ask, is there a way to have a drop down selection in certain fields for every row that is entered, and have required fields and also limit to list selections.

This log used to be done in Excel by someone else, i changed it because details where in all the wrong places.

I now want to try and do this in Excel again so people can make changes more easily later and understand what they are doing when they want to report on it.

I am going to use the Excel cell protection to sort out people entering data in the wrong places, but not sure how to do fields where people have to enter the correct data, or having a drop down list they must select from and cant enter info into the cell.

Also is there any functions in Excel that work the same way as BeforeUpdate does in Access, where the user cant leave a record and have it update if information is incorrect or missing.

Any help would be great

---------------------------------------

Neil

 

[gizmo1973]

You will need to use Validation.

I do something very similar.
In my first XX rows and in the column I want the user to choose from I put the allowed outcomes.
Then in the area I want the user to input I have this data validated to the cells above that contain the data.
Then I hide the first XX rows above and protec the sheet.
Where XX is the ammount of allowed outcomes/text
Hope this helps or if it's not clear let me know.


Regards, Phil

"If in doubt, hit it with an end user!

 

[Epsilon101]

Ah cool thanks for that, just checked out Data -> Validation now, luckily im using later than version 2000.

I also just remembered that maybe i can do this with a UserForm rather than having them input it into the rows.

Does anyone have any experience with this?

I dont need to run any queries on this data, it is just a log of information per month.

---------------------------------------

Neil

 

[01Patrik]

It's the "required" part that's tricky - validation doesn't stop you from leaving a cell blank.

SkipVought solved it with a nice piece of code in thread68-913447



// Patrik

 

[Epsilon101]

Gizmo.. in validation what settings did you do, i cant get it to work.
I enter info into cells c1:c10 and did data validation custom: =c1:c10 didnt work.

---------------------------------------

Neil

 

[01Patrik]

Use "list" instead of "custom" and it'll work.

// Patrik

 

[Epsilon101]

Ah thanks. Still pondering on this, probably best to have all cells protected and have a button for entering data into a new row by using a UserForm, and still go to the worksheet view to print it off.

---------------------------------------

Neil

 

[01Patrik]

Just a question of preferences... Whatever turns you on



// Patrik

 

[Epsilon101]

Can anyone point me in the right direction for info on this sort of thing, im not sure how im going to get this to work, required fields in an Excel Userform doesnt look too easy.

---------------------------------------

Neil

 

[01Patrik]

Take a look at

http://www.fontstuff.com/downloads/index.htm#excelvba

The introduction to VBA: Part 4 - user forms.

// Patrik