Restrict User Input

S

Sala

I'm in a bind....

I have a form that contains embedded drop down menus (are
they called lists?). I want the users to select an option
from the list but some users are getting creative and
finding ways to put non-list values in the cell. How do I
prevent this?

Sala
 
H

Harald Staff

Hi Sala

See if data validation will do what you want:
http://www.contextures.com/xlDataVal01.html

A worksheet isn't really a form in the strict meaning of the word. And
preventing incredible user actions is a complete science of its own. So
you're never 110% safe. But it's easier with a userform + VBA code than with
a worksheet.

HTH. Best wishes Harald
 
S

Sala

Hi Harald,

My current system uses data validation (I thought it had a
different name) and that's how I'm getting this problem.

What exactly is a userform? If can give me a few terms
here and there, I can use the assisstant to find out
the 'what' and 'how'.

Thanks
Sala
 
H

Harald Staff

Hi Sala

See Tom Ogilvy's reply to the thread "Create a Dialog Box" for userform
information. I wouldn't trust Clippy on that one :)

How are you experiencing problems with Data validation ? I know users can
paste to those cells and overwrite the whole thing, is that what happens ?

Best wishes Harald
 
S

Sala

Hey Harald,

I'll check out Tom's post.

You're exactly right. There are a lot of records and only
a handful of fields so most of the entering is
repetitive. Because of this, I want to give the users the
flexibility of 'copy paste' so they don't have to waste
time filling each box individually but at the same time, I
want to restrict them to using only the values in the data
validation.

Sala
 
S

Sala

Hi Harald,

I looked at the site but that's not the interface I want.
Insted of a 'form' view, it'd be easier for them if they
had it in 'datasheet' view.

I can't use MS Access because only a few users have it
(and I don't like the fact that you can only change one
cell at a time).

I want to keep it in an Excel worksheet, unlocked (so
copy/paste method can be used to quicken process) but at
the same time I'd like to mimic the Access table
property "LimitToList".

Any thoughts?
Sala
 
H

Harald Staff

Hi Sala

If the datasheet has to be an excel worksheet then you're now left with
macro programming/validation. And also risking that macros are disabled by
the user or the sysadmin, removing the whole logic.

Userforms use VBA, macros, programming code, and you can tame it to do
whatever cool things. But it has to be done, there are no "everything is
allowed and you'll be strictly validated doing it" methods available by
default. Liberty OR control, not both.

So either spend lots of hours (at $? each) programming this, buy a few
Access licenses (at $? each) or spend your users' time on awkward and
limited entries (at $? each), I believe that would be the options. Excel is
originally a spreadsheet, not a platform or a database, so she's not the one
to blame here.

(We may hope that someone proves me wrong here. Stranger things happen every
hour :)

Best wishes Harald
 
Top