PDA

View Full Version : Solved: Protect doesn?t work all the way



tiso
04-12-2006, 08:46 AM
I'm using the code ActiveSheet.Unprotect ("passwd") and ActiveSheet.Protect ("passwd") to unprotect/protect my worksheet. And that works ok with one exception: The dialog box shows that "allow selection" choices have both "locked" and "unlocked" checked, but I only want the "unlocked" checkbox marked. But every time I close the workbook and then open it and run the macro, both choises are marked again so I have to "manually" uncheck the "locked" checkbox before I run the macro. How come?

Ken Puls
04-12-2006, 09:01 AM
I believe that the "unlockedcells" is only a temporary setting, much like the "userinterfaceonly" is.

Try adding this to your ThisWorkbook code module, then save it, close it, and reopen it.

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.EnableSelection = xlUnlockedCells
Next ws
End Sub

HTH,

tiso
04-13-2006, 12:55 AM
I can?t make it work, maybe I put your sub in the wrong place? My sub looks like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

ActiveSheet.Unprotect ("passwd")

Cells.Interior.ColorIndex = xlNone
With ActiveCell
.EntireRow.Interior.ColorIndex = 37
.EntireColumn.Interior.ColorIndex = 37
.Interior.ColorIndex = 36

End With
ActiveSheet.Protect ("passwd")
End Sub

Shall I put your sub inside mine or...?

jindon
04-13-2006, 01:37 AM
Hi
the code is working for me

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

Me.Unprotect ("passwd")

Cells.Interior.ColorIndex = xlNone
With Target
.EntireRow.Interior.ColorIndex = 37
.EntireColumn.Interior.ColorIndex = 37
.Interior.ColorIndex = 36
End With
Me.Protect ("passwd")
Me.EnableSelection = xlUnlockedCells
End Sub

tiso
04-13-2006, 01:49 AM
Thanx, it works for me to :thumb

(I would mark the thread "Solved" if I only knew how :confused3 )

geekgirlau
04-13-2006, 06:38 AM
Done (don't worry, this isn't working correctly at the moment)