Force users to complete cells

  • Thread starter Thread starter s_a_burkey
  • Start date Start date
S

s_a_burkey

Trying to streamline some of our business processes and we've now put
the former paper-based absence forms into excel. Everything is ok but
in our testing phase we realised that 'some' managers are missing out a
number of key cells - is it possible to somehow 'force' them into
completing these cells (about 10 of them) before they are able to
save/save as /close the document?

Any help gratefully received ;-D

Pol
 
For each cell you could write a change event taking the manager to the next
intended cell for e.g.:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then
Range("A10").Select
End If
End Sub

After entering the contents for cell A1, cell A10 is then selected. And so
on

Then for the save event you can use something like:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

If IsEmpty(Sheet2.Range("A1")) Then
MsgBox "Cell A1 is empty"
Exit Sub
End If

If IsEmpty(Sheet2.Range("A10")) Then
MsgBox "Cell A10 is empty"
Exit Sub
End If
End Sub



Mangesh
 
Mangesh wrote...
....
Then for the save event you can use something like:
Click to expand...
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)

If IsEmpty(Sheet2.Range("A1")) Then
MsgBox "Cell A1 is empty"
Exit Sub
End If

If IsEmpty(Sheet2.Range("A10")) Then
MsgBox "Cell A10 is empty"
Exit Sub
End If
End Sub
Click to expand...
....

Several caveats. First, this requires macros be enabled. Since it's
very easy to disable macros, your approach on it's own is unreliable.
Second, if we assume the managers in question are lazy but perversely
clever SOBs (not an unreasonable assumption), what's to prevent them
from entering a single space character in each cell they couldn't be
bothered to enter previously but are now forced to navigate to?

The first step in any macro-based security needs to be *FORCING* users
to enable macros. The easiest way to do that is to use do-nothing udfs
like

Function udf(): End Function

then include udf() calls in *EVERY* formula in the workbook. If macros
are disabled, udfs return #NAME? errors, so all formulas would return
errors. You could & should go further and check whether this udf
returns #NAME?, and if so display error messages informing the
user-managers how they're screwing up (and it doesn't hurt to tell them
that a record of repeated screw-ups is being maintained - managers
understand fear, er, motivation). A formula like

=IF(COUNT(1/(ERROR.TYPE(udf())=5)),"You screwed up again!","")

or

=IF(COUNT(1/(ERROR.TYPE(udf())=5)),"Are you really so incompetent that
you can't enable macros on your own? The support number is
###-###-####.","")

Once you've gotten the attention of these user-managers, they'll enable
macros. At that point, your event handlers need to perform real data
validation, checking not that key entry cells are nonblank, but that
their entries match acceptable patterns. That's harder, and usually
requires some form of text pattern matching.
 
Mangesh wrote...
....

....

Several caveats. First, this requires macros be enabled. Since it's
very easy to disable macros, your approach on it's own is unreliable.
Second, if we assume the managers in question are lazy but perversely
clever SOBs (not an unreasonable assumption), what's to prevent them
from entering a single space character in each cell they couldn't be
bothered to enter previously but are now forced to navigate to?
Click to expand...

Agreed. Perhaps the question you have not asked here is ... do we really
"need" those extra cells (10 lots of extra data per record ? ... that
doesn't sound necessary to me :) ).
If you do, can you enter things by default (example ... charge their admin
accounts with the absence by default ... unless they enter a valid code that
is different :)).

There are many ways to encourage good behaviour ... if you really need to :)
The first step in any macro-based security needs to be *FORCING* users
to enable macros. The easiest way to do that is to use do-nothing udfs
like

Function udf(): End Function

then include udf() calls in *EVERY* formula in the workbook. If macros
are disabled, udfs return #NAME? errors, so all formulas would return
errors. You could & should go further and check whether this udf
returns #NAME?, and if so display error messages informing the
user-managers how they're screwing up (and it doesn't hurt to tell them
that a record of repeated screw-ups is being maintained - managers
understand fear, er, motivation). A formula like

=IF(COUNT(1/(ERROR.TYPE(udf())=5)),"You screwed up again!","")

or

=IF(COUNT(1/(ERROR.TYPE(udf())=5)),"Are you really so incompetent that
you can't enable macros on your own? The support number is
###-###-####.","")

Once you've gotten the attention of these user-managers, they'll enable
macros. At that point, your event handlers need to perform real data
validation, checking not that key entry cells are nonblank, but that
their entries match acceptable patterns. That's harder, and usually
requires some form of text pattern matching.
Click to expand...

You have to realise that enabling macros is a really bad idea for security
reasons. Perhaps the managers that refuse to supply all your extra data are
our GOOD managers :)




Bruce


-------------------------------------
The power of accurate observation is commonly called cynicism by those who have not got it.
- George Bernard Shaw
Cynic, n: a blackguard whose faulty vision sees things as they are, not as they ought to be.
- Ambrose Bierce

Caution ===== followups may have been changed to relevant groups
(if there were any)
 
XL is far from a good data entry system. If you must use it as such you
should store the data in a relational database format and use a form or
single worksheet for data entry. The user/manager can click a button
indicating the form/worksheet has been properly filled out. At that
point the code in your add-in should carry out the appropriate business
authentication and if the data are indeed OK add to / update the
database.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005
 
excel is a disease; lease ACCESS. it is TWICE as powerful and has much
better validation.

Excel is a disease. Excel is a disease.

Run away while you still can.

Aren't you tired of recreating the same XLS every week?
 
Back
Top