Lock ectire row by an value via VBA

[Nec11]

Hello to all,

I am trying to find an solution in Marco to lock an complete row if on column G:G, for an example is a text like "abcd" with pasword: <xxx> and if is text like "qwerty" with password <yyy>.

It is possible?

I have tryed with the attached VBA code but looks like is nor working

[VBA]Private Sub Worksheet_Calculate()
Dim LR As Long, i As Long
LR = Range("G" & Rows.Count).End(xlUp).Row
Me.Unprotect
For i = 1 To LR
With Range("G" & i)
If .Value = "abcd" Then .EntireRow.Locked = True
End With
Next i
Me.Protect
End Sub
[/VBA]

Regard.

[Teeroy]

Using .Find will be much faster than iterating over the range. You can also use .Findnext if you are expecting more than one.
And the password needs to be supplied to the protect and unprotect.

Try the following.

[vba]
Private Sub Worksheet_Calculate()
Dim Found As Range
Set Found = Columns("G").Find (what:="abcd", LookIn:=xlValues)
If Not Found Is Nothing And Len(Found) = Len("abcd") Then
Me.Unprotect "<xxx>"
Found.EntireRow.Locked = True
Me.Protect "<xxx>"
End If
End Sub[/vba]

[Kenneth Hobs]

I don't understand what you want. You listed two passwords and two strings to find. Is that for the same sheet or what?

If you like the find method, see this thread for a mulit-find example.
http://www.vbaexpress.com/forum/showthread.php?t=38802

Obviously, if your column G data is by formula, the Calculate event would work fine. That can be tweaked more if a formula has dependents. If it is by manual entry, a Change event would be more prudent.

Set the Userface option for a sheet's Protect in the workbook's Open event so that your code can modify without the need for Protect and Unprotect.
e.g. In ThisWorkbook object:
[vba]Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Protect "ken", UserInterfaceOnly:=True 'True allows code to change data.
Next ws
End Sub[/vba]
A simple attached example file usually helps us help more.

[Nec11]

As you asked for an attachment.

I have tryed the code but not succeded.

Any help is welcomed

Regards

[GTO]

Hi there,

.Protect works on the entire worksheet, not one cell, column, or row. So, whatever password is last supplied when protecting the sheet, will unlock the entire sheet. Does that make sense?

Mark

[Teeroy]

The default condition of cells in a worksheet is locked. To achieve what you want you need to unlock all the cells in the worksheet before you put the macro onto the sheet. That way only the cells that you specifically lock will be locked.

[Nec11]

and the solution will be ?!?!

[Kenneth Hobs]

First you need to unprotect all cells as Teeroy said.

You then need to understand what GTO said. Password protect is by, vbaproject, workbook, and worksheet(s).

Assuming that you want sheet protection, only one password can be active at once. I guess you could make the code change to another password but keeping track of the active one may be a daunting task if you have more than just the two simple passwords.

IF you just want the two passwords, that can be done. Of course unprotecting manually would be by guessing one of the two initially as the code would do as well.

Of course if you were going to use such simple passwords, hacker code can guess and then reset those.

So what is it to be, two sheet passwords plus none or many passwords and let hacker code reset "simple" passwords if needed? The latter solution would make manual password resetting near impossible without the hacker code.

[Nec11]

Easy to say, really difficult for me to do it becaus I do not have the knlowdge how to do it.

I just wont that lines with one propery to have one password and other lines with an diferent propriety to have a different password. It is not really important if the entire workbook is locked or not.

That's my problem and I dont'k now how to solve it becaus I do not have the knoledge how to do it.

Regards.

[Teeroy]

You can only have one password for each sheet. You can't get around that. Have a read of http://www.cpearson.com/excel/Protection.aspx.

To do what you asked for, but with one password, carry out the following steps.

• Unlock "Sheet1"
• go to the immediate window in the Visual Basic Editor and type (or copy in)

[vba]Sheets("Sheet1").Cells.Locked = False[/vba] This unlocks all the cells in "sheet1"

• Go to the code container for "Sheet1"
• Delete all the code you have there currently
• Copy in the following code

[vba]Private Sub Worksheet_Change(ByVal Target As Range)
Dim Found As Range, Found2 As Range, sText As String, sPwd As String
sText = "abcd"
sPwd = "xxx"
Set Found = Columns("G").Find(what:=sText, LookIn:=xlValues)
If Not Found Is Nothing Then
Set Found2 = Found
Else
Exit Sub
End If
Do
If Not Found2 Is Nothing And Len(Found2) = Len(sText) Then
With Found2
.Parent.Unprotect Password = sPwd
.EntireRow.Locked = True
.Parent.Protect Password = sPwd
End With
End If
Set Found2 = Columns("G").FindNext(Found2)
If Found2.Address = Found.Address Then Exit Do
Loop
End Sub[/vba]