[RESOLVED] Scan for Locked Cells

[Static]

I have code that scans for locked cells.. only problem is it says they are ALL locked.

I have range(A1:C13) as not locked.. and the worksheet is protected.
Now I can change values in that range but no where else...
but code returns ALL cells as locked??

whats wrong with this:

Sub ScanCell()
Dim WB As Workbook
Dim WS As Worksheet
Dim tWS As Worksheet
Set WB = ActiveWorkbook
Dim sRange_Col As String
Dim sRange_Row As Long
Dim tmp As String
Dim tmpR() As String
Dim lRow As Integer
Dim lCol As Integer
lRow = 2
lCol = 64
WB.Worksheets.Add Sheets(1)
Set tWS = ActiveSheet
tWS.Name = "LOCKED CELLS"
For Each WS In WB.Worksheets
If WS.Name <> tWS.Name Then
tWS.Range("A1") = "LOCKED CELLS"
lRow = 2
lCol = lCol + 1
tmp = Replace(WS.UsedRange.Address, "$A$1:", "")
tmpR = Split(tmp, "$")
sRange_Col = tmpR(1)
sRange_Row = tmpR(2)
For col = 65 To Asc(sRange_Col)
For Row = 1 To sRange_Row
If Range(Chr(col) & Row).Locked Then
tWS.Range(Chr(lCol) & lRow) = WS.Name & "!" & Chr(col) & Row
lRow = lRow + 1
End If
Next
Next
End If
Next
End Sub


Thanks!!

[DKenny]

If your row number is greater than 9, then this line of code will alyways give you a row number of 1 , so you're not really checking your whole range ;)

E.g Cell C$14; tmpR = C14; sRange_Row = 1


sRange_Row = tmpR(2)

[Static]

sorry, but you are incorrect


tmp = Replace(WS.UsedRange.Address, "$A$1:", "") 'Used Range is $A$1:$K$23
'tmp now = "$K$23"
tmpR = Split(tmp, "$") 'Splits by $
sRange_Col = tmpR(1) 'Now = K
sRange_Row = tmpR(2)'Now = 23


;) the output also shows the entire range.. if I was not scanning the whole sheet.. the output would not show the entire sheet :)

[DKenny]

Doh - it is an array, not a variable - my bad.

[Static]

:) no problem..
You did make me catch something though.. I changed code now to scan ONLY the used range.. not A1 through Used Upper range...
new code:

Sub ScanCell()
Dim WB As Workbook
Dim WS As Worksheet
Dim tWS As Worksheet
Set WB = ActiveWorkbook
Dim sRange_Col1 As String
Dim sRange_Row1 As Long
Dim sRange_Col2 As String
Dim sRange_Row2 As Long
Dim tmp As String
Dim tmpR1() As String
Dim tmpR2() As String
Dim lRow As Integer
Dim lCol As Integer
lRow = 2
lCol = 64
WB.Worksheets.Add Sheets(1)
Set tWS = ActiveSheet
tWS.Name = "LOCKED CELLS"
For Each WS In WB.Worksheets
If WS.Name <> tWS.Name Then
tWS.Range("A1") = "LOCKED CELLS"
lRow = 2
lCol = lCol + 1
tmp = WS.UsedRange.Address
If InStr(tmp, ":") Then
tmpR1 = Split(tmp, ":")
tmpR2 = Split(tmpR1(1), "$")
sRange_Col2 = tmpR2(1)
sRange_Row2 = tmpR2(2)
tmpR2 = Split(tmpR1(0), "$")
sRange_Col1 = tmpR2(1)
sRange_Row1 = tmpR2(2)
Else
tmpR1 = Split(tmp, "$")
sRange_Col1 = tmpR1(1)
sRange_Row1 = tmpR1(2)
sRange_Col2 = tmpR1(1)
sRange_Row2 = tmpR1(2)
End If
For col = Asc(sRange_Col1) To Asc(sRange_Col2)
For Row = sRange_Row1 To sRange_Row2
If Range(Chr(col) & Row).Locked Then 'HERE IS WHERE IT DOESNT SEEM TO WORK
tWS.Range(Chr(lCol) & lRow) = WS.Name & "!" & Chr(col) & Row
lRow = lRow + 1
End If
Next
Next
End If
Next
End Sub

[DKenny]

I think the problem is that when you are checking the cell to see if it is locked you are not refrencing the "WS" worksheet. Here's your code with a WITH statement added which ensures the .Range(Chr(col) & Row) refers to the correct sheet.

Hope this is right after my last post here.... :blush:

Sub ScanCell()
Dim WB As Workbook
Dim WS As Worksheet
Dim tWS As Worksheet
Set WB = ActiveWorkbook
Dim sRange_Col As String
Dim sRange_Row As Long
Dim tmp As String
Dim tmpR() As String
Dim lRow As Integer
Dim lCol As Integer
lRow = 2
lCol = 64
WB.Worksheets.Add Sheets(1)
Set tWS = ActiveSheet
tWS.Name = "LOCKED CELLS"
For Each WS In WB.Worksheets
With WS
If Left(.Name, 4) <> "LOCK" Then
tWS.Range("A1") = "LOCKED CELLS"
lRow = 2
lCol = lCol + 1
tmp = Replace(.UsedRange.Address, "$A$1:", "")
tmpR = Split(tmp, "$")
sRange_Col = tmpR(1)
sRange_Row = tmpR(2)
For col = 65 To Asc(sRange_Col)
For Row = 1 To sRange_Row
If .Range(Chr(col) & Row).Locked = True Then
tWS.Range(Chr(lCol) & lRow) = WS.Name & "!" & Chr(col) & Row
lRow = lRow + 1
End If
Next
Next
End If
End With
Next
End Sub

[Static]

you're right! good catch!
changed it to:

If WS.Range(Chr(col) & Row).Locked = True Then

and it worked perfectly

Thanks DKenny!