locking cells from specific point to infinity
 

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

Anyone have any ideas?

Ken

locking cells from specific point to infinity
 

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)



On Sat, 12 Sep 2009 11:58:49 -0700, "Ken"
wrote:

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

Anyone have any ideas?

Ken

locking cells from specific point to infinity
 

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

"Chip Pearson" wrote in message
...
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)



On Sat, 12 Sep 2009 11:58:49 -0700, "Ken"
wrote:

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

Anyone have any ideas?

Ken

locking cells from specific point to infinity
 

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" wrote in message
...
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

"Chip Pearson" wrote in message
...
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)



On Sat, 12 Sep 2009 11:58:49 -0700, "Ken"
wrote:

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

Anyone have any ideas?

Ken

locking cells from specific point to infinity
 

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

"JLGWhiz" wrote in message
...
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" wrote in message
...
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

"Chip Pearson" wrote in message
...
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)



On Sat, 12 Sep 2009 11:58:49 -0700, "Ken"
wrote:

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

Anyone have any ideas?

Ken

locking cells from specific point to infinity
 

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

On Sat, 12 Sep 2009 14:38:51 -0700, "Ken" wrote:

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

"JLGWhiz" wrote in message
...
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" wrote in message
...
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

"Chip Pearson" wrote in message
...
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)



On Sat, 12 Sep 2009 11:58:49 -0700, "Ken"
wrote:

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

Anyone have any ideas?

Ken

locking cells from specific point to infinity
 

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

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

On Sat, 12 Sep 2009 14:38:51 -0700, "Ken"
wrote:

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

"JLGWhiz" wrote in message
.. .
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" wrote in message
...
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

"Chip Pearson" wrote in message
...
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)



On Sat, 12 Sep 2009 11:58:49 -0700, "Ken"
wrote:

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

Anyone have any ideas?

Ken

locking cells from specific point to infinity
 

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)



On Sat, 12 Sep 2009 16:41:54 -0700, "Ken"
wrote:

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

"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
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

On Sat, 12 Sep 2009 14:38:51 -0700, "Ken"
wrote:

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

"JLGWhiz" wrote in message
. ..
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" wrote in message
...
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

"Chip Pearson" wrote in message
...
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)



On Sat, 12 Sep 2009 11:58:49 -0700, "Ken"
wrote:

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

Anyone have any ideas?

Ken

locking cells from specific point to infinity
 

Thank you once again!!!!!

"Chip Pearson" wrote in message
...
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)



On Sat, 12 Sep 2009 16:41:54 -0700, "Ken"
wrote:

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

"Gord Dibben" <gorddibbATshawDOTca wrote in message
. ..
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

On Sat, 12 Sep 2009 14:38:51 -0700, "Ken"
wrote:

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

"JLGWhiz" wrote in message
.. .
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" wrote in message
...
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

"Chip Pearson" wrote in message
...
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)



On Sat, 12 Sep 2009 11:58:49 -0700, "Ken"
wrote:

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

Anyone have any ideas?

Ken