Solved: Why is this code not locking the cells in the selected row ?

[frank_m]

This selection change code turns protection on, but is not locking cells in Col's 1 thru 30 of the selected row, when I change selection.
Sure would appreciate someone setting me straight on how to do this.[vba]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Me.ProtectContents Then
Msgbox "Sheet is unprotecd" ' this verifies that the sheet is not protected
Activecell.EntireRow.Cells(1).Resize(, 30).Locked = True
'the command in the line above fails to lock cells in col's 1 thru 30 of the selected row
Me.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, userinterfaceonly:=True
End If
End Sub
[/vba]

[mgm05267]

Hi Frank,

As initial step, select whole sheet & lock the cells......

Then if any selection is made, the lock will not be unlocked.

By default, when you launch new excel file, all cells will be in locked mode.

Please clarify on your requirement so that we can help you out...

Regards,

MGM

[mdmackillop]

[VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Me.ProtectContents Then
Cells.Locked = False
MsgBox "Sheet is unprotecd" ' this verifies that the sheet is not protected
ActiveCell.EntireRow.Cells(1).Resize(, 30).Locked = True
'the command in the line above fails to lock cells in col's 1 thru 30 of the selected row
Me.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, userinterfaceonly:=True
End If
End Sub
[/VBA]

[frank_m]

Edit: Hi mdmackillop - didn't see your post right away.. Thanks for that.. I'll test it and let you know
---------------------

Thanks for the feedback mgm,

Yes If I lock all the cells they will stay locked if the sheet is protected. - Except that I unlock a row of cells in order to perform manual edits, as the code to unlock a cell runs nearly instantaneously, as compared to code to unprotect the sheet can take upwards of 1 second to 1 1/2 seconds for 100,000 rows and 30 columns. And another 1 second to re-protect. -- I know that may seem fast, but I find it to be annoying.

I have code that almost always works to reprotect the cell after I edit, but there are rare scenarios where a cell is occasionaly left unlocked, or the sheet is left unprotected, or both. -- That is why I need code that when the user clicks on a row, all of the cells in that row are automatically locked, and the sheets protection status is checked.

My code as I have it, is very near to correct. Just has a minor bug that I haven't been able to figure out.

-- As a side note: Protection set with Userinterfaceonly set to True, allows the locking and unlocking without unprotecting the sheet.

[frank_m]

HI again mdmackillop,

I have since modified my code to: as shown below, as it covers more situations, and it works, but I think both yours and my original version also work, as I have since spotted and fixed a bug in another selection change routine, that was under some circumstances causing a cell to become unlocked.

Thanks for your time.
[vba]
Dim rng As Range

Set rng = ActiveCell.EntireRow.Cells(1).Resize(, 30)
If Not rng.Locked Then
rng.Locked = True
ElseIf Not Me.ProtectContents Then
'MsgBox "Protection is off. - The code will re-protect the sheet after you leave this msgbox"
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, userinterfaceonly:=True
End If
[/vba]