frustration with protecting a sheet!

[samohtwerdna]

alright, I am over trying to protect my worksheet properly!

I have this module so that I can turn on and off the Protection when I need to Sort - or adjust things via VBA:

[VBA]Public Sub ProtectionSwap(Optional ByVal fLock As Boolean = False)
Dim oWst As Excel.Worksheet
For Each oWst In ThisWorkbook.Worksheets
If fLock Then
oWst.Protect "My_Password", DrawingObjects:=False, Contents:=True, Scenarios:=True, AllowSorting:=True
ActiveSheet.EnableSelection = xlUnlockedCells
Else
oWst.Unprotect "My_Password"
End If
Next
End Sub[/VBA]

Now I want to let the user Select all unlocked cells but not the locked cells - but for some reason when I run the protection swap the user once again can select the locked cells.

Does anybody know what is going on?

[tkaplan]

how are you running the protection swap? are you calling it from another procedure or just running a macro?

[samohtwerdna]

I'm calling it from other procedures that wont work on a locked sheet.

BTW, I started with just a simple condition to unprotect then protect. So when I kept getting the select locked cells activated - I figured I need to record a macro to figure out how excel wants to turn off the locked cells. That's where I got the "ActiveSheet.EnableSelection = xlUnlockedCells"

I'm thinking I need to set all selections to false then re-enable the selection of the unlocked cells - Is that right?

[tkaplan]

after you call the procedure, are the sheets actually protected?
i tried creating a workbook, unlocking only some cells and then ran a macro just like this:
[VBA]
macro1()
activeworkbook.Protect Password:="hello", DrawingObjects:=False, Contents:=True, Scenarios:=True, AllowSorting:=True
ActiveSheet.EnableSelection = xlUnlockedCells
end sub
[/VBA]

this did protect the sheet and only allowed the user to select unlocked cells. so i'm thinking the problem is somewhere with the condition that you are setting of "If fLock Then " that fLock is being passed as false so it is just bypassing that.

try inserting the line msgbox("protecting sheet") after the flock condition to see if the procedure is not entering that at all. hopefully we can pinpoint the problem better this way.

[samohtwerdna]

Ok I inserted the message box and when I ran this procedure:
[VBA]Private Sub cmdOK_Click()
' here is my call...
ProtectionSwap

ActiveWorkbook.Sheets(2).Activate
Range("A1").Select

If cboJobCode.Value = "" Then cboJobCode.Value = "N"

Do
If IsEmpty(ActiveCell) = False Then ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell) = True

With ActiveCell
.Value = cboJobCode.Value
.Offset(0, 1) = txtJobNo.Value
.Offset(0, 2) = cboDealer.Value
.Offset(0, 9) = txtFinish.Value
.Offset(0, 10) = txtJobDescription.Value
.Offset(0, 11) = Date
.Offset(0, 12) = txtSignedDate.Value
.Offset(0, 13) = txtJobName.Value
.Offset(0, 25) = txtValue.Value
End With

If CheckBoxDoor.Value Then
With ActiveCell.Offset(0, 3)
.Value2 = txtDrQ.Value
.NumberFormatLocal = """<""0""> Dr12?18"""
End With
End If

If CheckBoxDoor15.Value Then
With ActiveCell.Offset(0, 4)
.Value2 = txtDr15Q.Value
.NumberFormatLocal = """<""0""> Dr15x18"""
End With
End If

If CheckBoxDDCombo.Value Then
With ActiveCell.Offset(0, 5)
.Value2 = txtDDComboQ.Value
.NumberFormatLocal = """<""0""> DDCom"""
End With
End If

If CheckBoxStep.Value Then
With ActiveCell.Offset(0, 6)
.Value2 = txtStepQ.Value
.NumberFormatLocal = """<""0""> Step"""
End With
End If

If CheckBox5x7.Value Then
With ActiveCell.Offset(0, 7)
.Value2 = txt5x7Q.Value
.NumberFormatLocal = """<""0""> 5x7's"""
End With
End If

If CheckBoxOther.Value Then
With ActiveCell.Offset(0, 8)
.Value2 = txtOther.Value
.NumberFormatLocal = """<""0""> """
End With
End If


' ...and again we lock it up when we're done
ProtectionSwap True
End Sub[/VBA]

I got the message box twice - Also I realized that my code was working fine until I closed the worksheet and reopened it - the only code I call from the WorkBook_Open() is :
[VBA]Sub JobAlert()
Dim Cll As Range, AlertMsg As String, JobCells As Range
Dim ProcCol As Range, SignCol As Range, JobCol As Range

With Sheets("Short Order Schedule")
Set ProcCol = .Columns("G") 'date processed column
Set SignCol = .Columns("H") 'date signed column
Set JobCol = .Columns("E") 'job column (to tell user)
End With

For Each Cll In ProcCol.Parent.Range(ProcCol.Cells(3), ProcCol.Cells(65536).End(xlUp))
If DateAdd("m", 1, Cll.Value) <= Date And SignCol.Cells(Cll.Row).Value = "" Then
If JobCells Is Nothing Then
Set JobCells = JobCol.Cells(Cll.Row)
Else
Set JobCells = Union(JobCells, JobCol.Cells(Cll.Row))
End If
End If
Next
If Not JobCells Is Nothing Then
AlertMsg = "Jobs Needing Attention"
For Each Cll In JobCells.Cells
AlertMsg = AlertMsg & vbCrLf & Cll.Text
Next
JobCells.Parent.Select
JobCells.Select
MsgBox AlertMsg
End If
End Sub[/VBA]

So I am not sure why my protection settings get reset on open??

[tkaplan]

can you attach the sheet? or modify it that only the relevant code is in there and attach that?

[samohtwerdna]

Here is the worksheet sample

Hopefully it goes through.

Also I was thinking maybe it's on the "Save" that my protect settings get changed??

[tkaplan]

i'm not sure how much this helps, but i commented out the workbook open code and it still has the same problem - apparently when you open the workbook, some setting is being reset, but as far as i can tell, it is not by your code.

i did a little test with a workbook that i just unprotected a couple of cells and manually set the settings. i reopened it and the settings stayed fine. so it's not an excel fluke either.... (i know this must be so helpful)

there are a few things that i am trying to run but it is telling me that i am missing libraries, etc.
can you create a simplified version of this: just one sheet, with the getform button and that form and only the code that pertains to that form? this may help us help you...

[samohtwerdna]

Ok here is the skinny version with just the form and the protection code

[samohtwerdna]

Ah!! where did my attachment go?

Next try,

[tkaplan]

i tried simplifying it here as well. (attached is the simplified version)
i played around with on open and onclose events. i tried opening it in design view so that no macros run when i start up. still has the problem.

maybe someone else can help out...?