Tighter controls to prevent users from messing up

[TPD]

Hello,

I have an Excel worksheet that we use as our "Opportunity Form" in our large office. The salesman are responsible for filling this form out every week and submitting it to our office for review. What I'd love to add (if at all possible) is:

A way to prevent them from saving the file (under a new name) on their drive without filling out the required fields properly.

A control to prevent them from submitting the file (if I add a submit button) without filling in the required fields correctly.

A way to force them to fill the fields correctly without being able to advance to the next field. (probably impossible)

I know this all sounds crazy, but I have racked my brain on trying to add more controls for these people (shall I say numbskulls?) to for them to fill the revenue charts out correctly BEFORE sending it off. I have tried to make it simple with the validation restrictions, but they continue to find ways to add invalid entries and send it off.

I'm obviously a newbie in Excel and I never dreamed these guys would test me to the MAX on every stinking field on this sheet!!

Is there a way to do "ANY" of the above steps in Excel?

Thanks!!!
TD

 

[acw]

Todd

1) Have you looked at using data validation in the cells so they can only select from a distinct set of inputs.
2) You could also code something in the workbook.beforesave and/or workbook.beforeclose to do some testing and make sure entries are completed.
3) Another option is to have a user input form so they have to complete the form rather than the workbook directly. That way, the form would have to be completed (again using restricted entries) before the data was saved to the sheet.

HTH

Tony

 

[XLGibbs]

A way to prevent them from saving the file (under a new name) on their drive without filling out the required fields properly.

Yes, combinations of using either an .xlt (template file) or distributing a read only file, that can only be saved folloing data validation. Data validation does not involve code, and you may find this useful in allowing either certain values or valid text, and you can also add conditional formatting without code to change a color of cell if the entered result is not valid. Conditional formatting can have considerable options as well.


A control to prevent them from submitting the file (if I add a submit button) without filling in the required fields correctly.

A way to force them to fill the fields correctly without being able to advance to the next field. (probably impossible)


See above, data validation can be set to only allow correct entries, determined by how you set up the validation.


I know this all sounds crazy, but I have racked my brain on trying to add more controls for these people (shall I say numbskulls?) to for them to fill the revenue charts out correctly BEFORE sending it off. I have tried to make it simple with the validation restrictions, but they continue to find ways to add invalid entries and send it off.

There are two things that are infinite: human ignorance and the universe. And I am not certain about the latter.

Unfortunately, no matter how "idiot proof" your file is, they keep making better idiots. Consider using the forms toolbar and creating lists of known fixed data and requiring them to select a choice from the drop down (see data validation)

I'm obviously a newbie in Excel and I never dreamed these guys would test me to the MAX on every stinking field on this sheet!!

No worries. One step at a time. There are many different ways to approach this using either formula or VBA solutions, although many of your desired results would be faster solved using VBA, but would require the users to enable macros. Also, files distributed through some companies networks that contain macros get stopped by internal email firewalls, so that may be an issue as well to consider before using code.


Is there a way to do "ANY" of the above steps in Excel?

 

[Tazguy37]

I'd bet that everything you're asking for can be done.

Here's a thread on not being able to save if a cell is a certain value. I'm sure this could be adapted for what you want to do. Something along the lines of:

Dim cell As Range

    For Each cell In Range("A1,B7,C6")
        If cell.Value = "" Then
            MsgBox "Cell " & cell.Address & " is blank."
            Exit Sub
        End If
    Next cell
    ActiveWorkbook.Save

Hope that helps!

 

[TPD]

Thanks!

Thanks for the support and tips.

I have tried validation and these guys are not filling in the Millions and Thousands correctly for dollar value capture fields,..However,..I will re-visit it closer to see if I can tighten it up anymore. Template idea is cool too so long as it allows them to enter values in the fields etc. I;m sure it does or you would not have recommended it.

That code looked good too,..but again,..i wouldn't even know "where" to add it as everthing I do in Excel must be based on instructions that are written verbatim. I guess I'm as dumb as the sales folks I'm referencing too!!!

 

[tactps]

If you come up with specific ideas based on the suggestions here, post back and I'm sure we can help you.

But note:
Build a better mousetrap and you'll find a better mouse
or as I prefer:
Make something idiot-proof and you'll find a bigger idiot.

Good luck Todd!