locking cells from specific point to infinity

[Ken]

I would like to lock all cells starting from J1 to infinity and from A62 to
infinity using VB.

Anyone have any ideas?

Ken

 

[Chip Pearson]

By default, all cells are locked, so you first need to unlock
everything and then selectively lock the desired ranges. E.g.,

Sub AAA()
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
With WS
.Cells.Locked = False
.Cells(1, "J").Resize(.Rows.Count, 1).Locked = True
.Cells(62, "A").Resize(.Rows.Count - 61).Locked = True
.Protect
End With
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Ken]

Thanks for your reply!

While attempting to figure out your code, I'm curious as to why:
1) are you using Resize
2) is the Rows.Count -61, rather then Rows.Count, 62

Ken

 

[JLGWhiz]

Run the macro below with a active blank sheet and you will see in the
message box that with - 61 it selects from A62 through the last row of the
sheet, but with - 62, the last row is not part of the selection.

 

[Ken]

Sorry, I see that I should have explained this better.

I only want the user to have access to A1 through I60
So, what I would like is to lock all rows and columns down and to the right
of J1.
Also all rows and columns down and to the right of A61

Once again, my appologies for not being more specific in the first place.

Ken

 

[Gord Dibben]

Sub lockit()
Cells.Locked = True
Range("A1:I61").Locked = False
End Sub

Don't forget...........the worksheet must be protected after the above runs.


Gord Dibben MS Excel MVP

 

[Ken]

Wonderful...thank you!!!

I had to add the UnProtect or I would get and error if I ran this when
Protection was on. Also fine tuned it a little:

Sub SetCellProtection()

Dim WS As Worksheet
Set WS = Worksheets("Calendar")

With WS
.Unprotect
.Cells.Locked = True
.Range("G1").Locked = False
.Range("A3:A51").Locked = False
.Range("C5:I5, C7:I7, C9:I9, C11:I11, C13:I13, C15:I15").Locked =
False
'Next months range
.Range("C20:I20, C22:I22, C24:I24, C26:I26, C28:I28,
C30:I30").Locked = False
'Next months range
.Range("C35:I35, C37:I37, C39:I39, C41:I41, C43:I43,
C45:I45").Locked = False
'Next months range
.Range("C50:I50, C52:I52, C54:I54, C56:I56, C58:I58,
C60:I60").Locked = False
.Protect

End With

End Sub

 

[Chip Pearson]

I had to add the UnProtect or I would get and error if I ran this when

Protection was on.

When you protect the worksheet via code, you can specify the
UserInterfaceOnly parameter as True. This setting prevents the user
from changing a locked cell, but allows VBA to do whatever it wants
with no restrictions.

ThisWorkbook.Worksheets("Sheet1").Protect UserInterfaceOnly:=True

This setting is NOT preserved if you close and reopen the workbook, so
you will likely want to set it when the workbook is opened. In the
ThisWorkbook module, use code such as the following:

Private Sub Workbook_Open()
Worksheets("Sheet1").Protect UserInterfaceOnly:=True
' repeat for desired worksheets
End Sub


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Ken]

Thank you once again!!!!!

When you protect the worksheet via code, you can specify the
UserInterfaceOnly parameter as True. This setting prevents the user
from changing a locked cell, but allows VBA to do whatever it wants
with no restrictions.

ThisWorkbook.Worksheets("Sheet1").Protect UserInterfaceOnly:=True

This setting is NOT preserved if you close and reopen the workbook, so
you will likely want to set it when the workbook is opened. In the
ThisWorkbook module, use code such as the following:

Private Sub Workbook_Open()
Worksheets("Sheet1").Protect UserInterfaceOnly:=True
' repeat for desired worksheets
End Sub


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)