Can this be done better? - 'selective protection'

[michael.beckinsale]

Hi All,

I have a requirement whereby l need to make only particular sheets
available for data entry dependent on several different passwords /
logons.

Complications:

1) All sheets need to be protected so that data entry is restricted to
unlocked cells.
2) All sheets must be visible (users need to follow the audit trail
even if a department is not under their jurisdiction.)

I am sure that somebody has done similar to this in the past and
wonder if anybody can provide or suggest a solution. If not can they
improve on the following strategy? (assumes all sheets protected on
opening)

1) Workbook_open event userform for password entry
2) Password 'mapped' to select case statement
3) Select case statement changes the 'enable selection' property in
all unavailable 'input' type sheets to xlNoSelection
4) Reset selection property on Workbook_close

The problem l can forsee with this strategy is that the workbook would
have to be saved / closed to change user permissions unless l provide
a method within the workbook via button / menu.

All contributions welcome

Regards

Michael

 

[dan dungan]

Hi Michael,

I'm having difficulty visualizing your scenario.

Is there data entry required on every sheet?

How can you make only particular sheets
available for data entry dependent on several different passwords /
logons and yet per your rule 2, have all sheets visible.

How does a user follow the audit trail?

How do you know they followed it?

How many different passwords is several?

Does a user have more than one password?

Is all the data entry completed in the user form?

How do users obtain the data to enter?

Dan

 

[michael.beckinsale]

Hi Dan,

Thanks for responding.

Let me try to clarify & respond to your questions.

This spreadsheet is an 'output costing' model which maps the costs
from accounting ledgers to predefined outputs through a series of
matrix & cost drivers. Thus costs are cascaded down from lets say
'head office' to regional offices, costs can be cross charged by
regional offices and finally the resultant costs are apportioned via
drivers to outputs. This has been completed and works as required.

I am now looking at deployment & internal housekeeping. Initially
there will only be 5 or 6 people accessing the model, each one will be
responsible for a particular area, lets say head office & regions 1 to
5. Because the final figure to be apportioned by the output driver
consists of direct costs from the accounting system + contributions
from head office + regions 1 to 5 it is desirable that the user logged
in can only change the drivers associated with their area. This
ensures that they cannot change other area's drivers to reduce /
change the amount apportioned to them. You can then see it is also
desirable that they can follow the model's computations to see how the
amount apportioned to their area has been arrived at, thus they need
to see sheets not associated directly with their area. If they are not
happy with that amount internal negotiations will take place to
validate / change the cost driver basis.

Now to answer your questions specifically (assume sheets are protected
with relevant cells unprotected to allow data entry),

Data entry is required on approx 70% of the total no of sheets but
surely this is irrelevant.

As per original posting irrelevant sheets made UNavailable by changing
sheet property to xlNoSelection. Thus they can see the sheet but not
access it.

Audit Trail was a descriptive used in original posting. Initial users
are familiar with Excel and will follow the formula's and will have
been schooled in the model's operation.

Choice user has if they are interested and irrelevant.

Probably 5 or 6. My initial thoughts are 1 for each area. This might
change as the model develops and its use gets more widespread. It will
very much depend on company policy & internal housekeeping.

No

No

From external sources but not database / software related.

I hope this helps.

Regards

Michael

 

[dan dungan]

Hi Michael,

I just noticed your response. I'll look at this in the morning and see
if I can be of any help.

Dan

 

[michael.beckinsale]

Hi Dan,

Just to let you know l have completed the coding along the lines in my
original posting and whilst l have still to complete exhaustive
testing everything appears to be working as required.

I will probably apply a workbook level password as well to stop users
changing the workbook structure, sheet names & sheet colours.

In case you are interested this is the code & assumptions:

1) The cells in each of the sheets in the workbook are locked /
unlocked as required
2) Workbook_Open event protects all sheets (ie Call ProtectShts)
3) Workbook_open event launches logon form
4) Logon form has 3 objects, textbox for password, login button,
cancel button
5) Button on 'Start Here' sheet allows user(s) to change logon whist
workbook is open.
6) Input sheets are specific colour
7) Sheet names are consistent format
8) Logon password determines which input sheets are set to either
xlNoRestrictions or xlNoSelection
9) User can have 3 attempts at logging on.

Form Code: (beware line wrapping & commenting)
__________________________________________
Private Sub cbCancel_Click()
Unload frmLogon
ThisWorkbook.Close SaveChanges = False
End Sub
___________________________________________
Private Sub cbLogin_Click()

Static LogCount As Integer
Dim ShtKey As String
Dim myWB As String
Dim DEV As String
Dim Admin As String
Dim CSD As String
Dim HMCI As String
Dim FD As String
Dim CHI As String
Dim LS As String
Dim EDU As String
Dim MyPwd As String

'Set the login tries
LogCount = LogCount + 1
'Set the passwords
DEV = "EE9855"
Admin = "ADMpwd"
CSD = "CSDpwd"
HMCI = "HMCIpwd"
FD = "FDpwd"
CHI = "CHIpwd"
LS = "LSpwd"
EDU = "EDUpwd"
'Assign password entered
MyPwd = tbPwd.Value
'Check it is valid
If _
MyPwd = DEV Or _
MyPwd = Admin Or _
MyPwd = CSD Or _
MyPwd = HMCI Or _
MyPwd = FD Or _
MyPwd = CHI Or _
MyPwd = LS Or _
MyPwd = EDU _
Then
'If valid do this
'........inform user of setup status
Application.StatusBar = "Please wait........setting up
workbook for your login profile."
'........set sheets to be made available
Select Case MyPwd
Case DEV
Call UnprotectShts
ShtKey = ""
Case Admin
Call UnprotectShts
ShtKey = ""
Case CSD
ShtKey = "CSD*"
Case HMCI
ShtKey = "HMCI*"
Case FD
ShtKey = "FD*"
Case CHI
ShtKey = "CHI*"
Case LS
ShtKey = "L&S*"
Case EDU
ShtKey = "EDU*"
End Select
'.......make sheets available / not available
If ShtKey <> "" Then
myWB = ThisWorkbook.Name
'.......if logon changed while workbook opened
' with DEV & Admin ensure shts protected
Call ProtectShts
'.............................................
Application.ScreenUpdating = False
For Each Sht1 In Workbooks(myWB).Worksheets
If Sht1.Tab.ColorIndex = 37 Then
If Sht1.Name Like ShtKey Or Sht1.Name Like
"Report*" Then
Sht1.EnableSelection = xlNoRestrictions
Else
Sht1.EnableSelection = xlNoSelection
End If
Sht1.Activate
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
Sht1.Range("A1").Select
End If
Next Sht1
End If
'.......tidy up & exit
Sheets("Start Here").Select
LogCount = 0
Unload frmLogon
Application.StatusBar = False
Else
'IF invalid do this
'.......close workbook if user has entered 3 wrong logons
If LogCount = 3 Then
MsgBox ("Invalid Password, the workbook will now close")
LogCount = 0
ThisWorkbook.Close SaveChanges = False
Unload frmLogon
Else
'.......inform user of wrong logon
MsgBox ("Invalid Password, you have " & 3 - LogCount & "
tries left.")
tbPwd.Value = ""
tbPwd.SetFocus
End If
End If

End Sub
________________________________________________________________
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
Integer)
'Stop user using 'X' button thus obtaining
'previous users permissions.
If CloseMode = vbFormControlMenu Then
MsgBox "You must use the 'Login' or 'Cancel' buttons."
Cancel = True
End If

End Sub
________________________________________________________________

If you have got this far l would be interested in your comments

Regards

Michael.