Select cells that are locked

[rochytall2000]

hi all, i need to be able to let the user copy and paste cells that are protected, how can I do this using VBA. I have created a macro to look at the VBA created when protecting spreadsheet from Tools menu but I can't replicate if using VBA. help is always appreciated, here's the code

If answer = vbYes Then
ActiveSheet.Unprotect "password"
Range("F7").Select
ActiveCell.FormulaR1C1 = Application.UserName & " " & Now
Cells.Select
Selection.Locked = True
Selection.FormulaHidden = True
Range("A1").Select

' Switch Highlighting
Range("F7").Select
Selection.Interior.ColorIndex = xlNone
Selection.Font.Bold = False
Range("F7").Select
With Selection.Interior
.ColorIndex = 2
.Pattern = xlGray25
.PatternColorIndex = 0
End With
With Selection.Font
.Bold = True
.Italic = False
End With

ActiveSheet.Shapes("Button 1").Visible = False
ActiveSheet.Protect "password", DrawingObjects:=True, Contents:=True, Scenarios:=True
'ActiveSheet.EnableSelection = xlUnlockedCells

Application.ScreenUpdating = False

'Lock Accounts Tab
Sheets("Accounts").Select
ActiveSheet.Unprotect "password"
Cells.Select
Selection.Locked = True
Range("A1").Select

ActiveSheet.Protect "password", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells

Application.ScreenUpdating = True
MsgBox "Template Locked"

End If


Thanks in advance
nagartath

 

[kmillen]

Locking and unlocking worksheets on a sheet-by-sheet basis is an option, but a more elegant method is to allow access through an individual's log-on name. The web site below has an excellent example.

http://blogs.officezealot.com/charles/archive/2004/12/10/3574.aspx

Create a function in your workbook that uses the function in the above web site and name your function Autpen(). This will run automatically when the workbook opens.

Add an array of legal usernames to a string array

Dim names(1 To 10) As String
names(1) = "billy"
names(20 = "sally"
...

and compare the login name to the predefined list.

Protect or unprotect based on a match.

Sub Protect()

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False
End Sub

Sub UnProtect()

ActiveWorkbook.UnProtect
ActiveSheet.UnProtect
End Sub