locking cells from specific point to infinity

  • Thread starter Thread starter Ken
  • Start date Start date
K

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
 
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)
 
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
 
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.
 
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
 
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
 
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
 
I had to add the UnProtect or I would get and error if I ran this when
Protection was on.
Click to expand...

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)
 
Thank you once again!!!!!

Chip Pearson said:
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)
Click to expand...
 
Back
Top