Worksheet protection

  • Thread starter Thread starter lestergit
  • Start date Start date
L

lestergit

I have an Excel workbook with numerous sheets that are relevant t
people in a department. They are required to fill in their schedule
start/finish times. This information is collated into a workshee
showing all the data for a week.

The problem I have is that each individuals start/finish times i
personal information to them. I can see it but other members of th
department cannot. Therefore I need to set a password for thei
individual sheets that prevents others from seeing the data. It isn't
problem to prevent others from altering the data but I cannot find a wa
of making that data invisible once it has been password protected.

Please help me 'cos it's driving me barmy
 
If the people in your department are even moderately competent (i.e.,
they can find their way to this newsgroup), they will be able to find a
way to bypass any protection you can put in an XL file.

Removing password protection for a worksheet is absurdly easy:

http://www.mcgimpsey.com/excel/removepwords.html

You're better off sending each of them their own sheet.
 
JE hit the nail on the head............there's lots of things you can do to
confuze and confound, and it will deter te honest folks, but if someone
really wants to get to your data, there's not much you can do to stop them,
(believe me, I've tried)..........and if you want insured security, you must
give them each separate books. It's really not that hard to consolidate
them at the end to put on a master sheet if you want.............

Vaya con Dios,
Chuck, CABGx3
 
Hi lestergit!

Following on from Chuck's recommendation to provide separate sheets.

Ron de Bruin has an excellent tool that faciltates sending the
separate sheets to the right people.

See:
http://www.rondebruin.nl/sendmail.htm


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
CLR said:
JE hit the nail on the head............there's lots of things you
can do to
confuze and confound, and it will deter te honest folks, but if
someone
really wants to get to your data, there's not much you can do to
stop them,
(believe me, I've tried)..........and if you want insured security,
you must
give them each separate books. It's really not that hard to
consolidate
them at the end to put on a master sheet if you want.............

Vaya con Dios,
Chuck, CABGx3


message
Click to expand...
 
Unfortunatley separate workbooks is not an option. The data needs to b
collated and I know that the users will fiddle with the file names an
locations and break any links I set up.

I am not concerned about the weakness of Excel password protection. th
data is not particularly sensitive but there is a better chance o
getting the IT sceptic members involved if the data appears reasonabl
secure.

I have the beginnings of a simple solution. I have drawn a rectangl
around the data and use macros linked through a button that turn th
fill from clear to white and vice versa.

I could then tell them to use the Tools menu to password protect bu
would prefer this to be part of the macro that sets the rectangle fill
I have tried recording password setting but it does not let me sto
recording until the password has been set. I want to bale out when th
password dialog appears.

Any more ideas please, and thanks for the good advise so fa
 
Since you haven't gotten a better answer..........maybe some of this will
help.........I used it to only allow certain columns to be edited by certain
users............most of it came from help in these newsgroups.........

hth
Vaya con Dios,
Chuck, CABGx3



Sub UserName()
'Command macro to allow only specified users to edit specific columns
'This macro runs automatically upon Workbook being opened

Dim UserName

ActiveSheet.Unprotect Password:=("terribill")
'Lock all columns A:K
Columns("A:K").Select
Selection.Locked = True
With Selection.Interior
.ColorIndex = 38
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("A1").Select
'Re-protect the worksheet
ActiveSheet.Protect Password:=("terribill"), DrawingObjects:=True,
Contents:=True, Scenarios:=True
'Display Input Bos requesting UserName
UserName = InputBox("Enter your UserName")
'Run specific macros in accordance with UserName entered
If UserName = ("BBill") Then
BBill
ElseIf UserName = ("MTerri") Then
MTerri
Else
MsgBox "Unauthorized UserName"
End If

End Sub
__________________________________

Sub MTerri()
'This macro allows Terri access to the worksheet, and authorizes her to
edit
'columns A thru J

ActiveSheet.Unprotect Password:=("terribill")
'Clears locking on columns A:F and colors them yellow indicating
"editable"
Columns("A:F").Select
Selection.Locked = False
Selection.FormulaHidden = False
With Selection.Interior
.ColorIndex = 19
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
'Clears locking on column K and colors it yellow indicating "editable"
Columns("k").Select
Selection.Locked = False
Selection.FormulaHidden = False
With Selection.Interior
.ColorIndex = 19
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

'Locks columns G:J and colors them pink indicating "no editing allowed"
Columns("G:J").Select
Selection.Locked = True
Selection.FormulaHidden = False
With Selection.Interior
.ColorIndex = 38
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
'Selects header area and clears cell colors for readability
Range("a1:k5").Select
Selection.Interior.ColorIndex = xlNone
'Re-protects the worksheet
ActiveSheet.Protect Password:=("terribill"), DrawingObjects:=True,
Contents:=True, Scenarios:=True
Range("a1").Select 'Clears all highlighting

End Sub
_____________________________


Sub BBill()
'This macro allows Bill access to the worksheet, and authorizes him to edit
'columns G thru K

ActiveSheet.Unprotect Password:=("terribill")
'Locks columns A:F and colors them pink indicating "no editing allowed"
Columns("A:F").Select
Selection.Locked = True
Selection.FormulaHidden = False
With Selection.Interior
.ColorIndex = 38
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
'Clears locking on columns A:F and colors them yellow indicating
"editable"
Columns("G:K").Select
With Selection.Interior
.ColorIndex = 19
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Selection.Locked = False
Selection.FormulaHidden = False
'Selects header area and clears cell colors for readability
Range("A1:K5").Select
Selection.Interior.ColorIndex = xlNone
'Re-protects the worksheet
ActiveSheet.Protect Password:=("terribill"), DrawingObjects:=True,
Contents:=True, Scenarios:=True
Range("A1").Select 'Clears all highlighting
End Sub
 
Don't drop the @#$% subject line in your posts.

lestergit wrote...
Unfortunatley separate workbooks is not an option. The data
needs to be collated and I know that the users will fiddle with
the file names and locations and break any links I set up.
Click to expand...

Your concerns are groundless. If you gave each user a separate, singl
worksheet workbook of their own to fill in, you could have them sen
you back copies. If you put them all in the same directory, you coul
then 'harvest' the worksheets from each file without having to car
what they've dreamed up as filenames, moving those worksheets into
single master file you could then collate and consolidate to you
heart's content.

The macros to do this would be MUCH SIMPLER and much more reliable tha
futzing around trying to implement the spreadsheet equivalent o
individual record-level locking in database tables.
I am not concerned about the weakness of Excel password
protection. the data is not particularly sensitive but there is a
better chance of getting the IT sceptic members involved if the
data appears reasonably secure.
Click to expand...

If your IT people put any faith into Excel's internal passwor
security, then you are blessed with some pretty ignorant IT staff.

If you sent each user a separate file, you'd have as much privac
protection as Excel can provide. If you deferred consolidation an
collation until after you've received copies of these files from you
users, you could minimize the need for VBA, and what necessary codin
remains would be simpler because it wouldn't need to cope with th
vagaries of user inputs.

You are fooling yourself if you believe that a single workbook fo
everyone design can be made to work reasonably well. However, som
people need to learn from experience, so don't let me stop you i
you're set on this approach
 
Back
Top