Lock and Unlock Cells

Hello All,

I am trying to get my sheets to work as follows:

Unprotect the worksheet (with password)

Protect cell range B16,D45

Unprotect cell range N16,N45

Protect sheet (with password)

Then move to the next sheet in the Workbook and repeat the process.
There are 20+ Worksheets in the Workbook.

Regards
Peter

One way:

Const sPWORD As String = "drowssap"
Dim ws As Worksheet
For Each ws In Worksheets
With ws
.Unprotect Password:=sPWORD
.Range("B16:D45").Locked = True
.Range("N16:N45").Locked = False
.Protect Password:=sPWORD
End With
Next ws

In article <[email protected]>,
"Peter" <[email protected]> wrote:

> I am trying to get my sheets to work as follows:
>
> Unprotect the worksheet (with password)
>
> Protect cell range B16,D45
>
> Unprotect cell range N16,N45
>
> Protect sheet (with password)
>
> Then move to the next sheet in the Workbook and repeat the process.
> There are 20+ Worksheets in the Workbook.

Hello JE,

Thanks for the info...

I have tried the code you suggested, but to no avail....Yet.

When I reopen the worksheet saved when exiting, i get a Run TIme Error 1004
- Unable to set the locked property of the range class error, and the
breakpoint rests on the line .Range("B16:D45").Locked = True

I have already added the "Trust Access To Visual Basic Project" as it
suggested..still no luck.

Any suggestions as to what I may have done wrong?

Regards
Peter




"JE McGimpsey" wrote:

> One way:
>
> Const sPWORD As String = "drowssap"
> Dim ws As Worksheet
> For Each ws In Worksheets
> With ws
> .Unprotect Password:=sPWORD
> .Range("B16:D45").Locked = True
> .Range("N16:N45").Locked = False
> .Protect Password:=sPWORD
> End With
> Next ws
>
> In article <[email protected]>,
> "Peter" <[email protected]> wrote:
>
> > I am trying to get my sheets to work as follows:
> >
> > Unprotect the worksheet (with password)
> >
> > Protect cell range B16,D45
> >
> > Unprotect cell range N16,N45
> >
> > Protect sheet (with password)
> >
> > Then move to the next sheet in the Workbook and repeat the process.
> > There are 20+ Worksheets in the Workbook.
>

Assuming you are doing this in the Workbook_Open event, Try

Private Sub Workbook_Open()
Const sPWORD As String = "drowssap"
Dim aws as Worksheet
Dim ws As Worksheet
Application.ScreenUpdating = False
set aws = ThisWorkbook.Activesheet
For Each ws In ThisWorkbook.Worksheets
With ws
.Activate
.Unprotect Password:=sPWORD
.Range("B16:D45").Locked = True
.Range("N16:N45").Locked = False
.Protect Password:=sPWORD
End With
Next ws
aws.Activate
Application.ScreenUpdating = True
End Sub

--
Regards,
Tom Ogilvy

"Peter" <[email protected]> wrote in message
news:[email protected]...
> Hello JE,
>
> Thanks for the info...
>
> I have tried the code you suggested, but to no avail....Yet.
>
> When I reopen the worksheet saved when exiting, i get a Run TIme Error
1004
> - Unable to set the locked property of the range class error, and the
> breakpoint rests on the line .Range("B16:D45").Locked = True
>
> I have already added the "Trust Access To Visual Basic Project" as it
> suggested..still no luck.
>
> Any suggestions as to what I may have done wrong?
>
> Regards
> Peter
>
>
>
>
> "JE McGimpsey" wrote:
>
> > One way:
> >
> > Const sPWORD As String = "drowssap"
> > Dim ws As Worksheet
> > For Each ws In Worksheets
> > With ws
> > .Unprotect Password:=sPWORD
> > .Range("B16:D45").Locked = True
> > .Range("N16:N45").Locked = False
> > .Protect Password:=sPWORD
> > End With
> > Next ws
> >
> > In article <[email protected]>,
> > "Peter" <[email protected]> wrote:
> >
> > > I am trying to get my sheets to work as follows:
> > >
> > > Unprotect the worksheet (with password)
> > >
> > > Protect cell range B16,D45
> > >
> > > Unprotect cell range N16,N45
> > >
> > > Protect sheet (with password)
> > >
> > > Then move to the next sheet in the Workbook and repeat the process.
> > > There are 20+ Worksheets in the Workbook.
> >

Hello Tom,
Thanks for the code. It's doing the exact same thing though.
I have removed all other macro's from my workbook, and it's still the same.

Any idea's why are welcome..

Regards
Peter


"Tom Ogilvy" wrote:

> Assuming you are doing this in the Workbook_Open event, Try
>
> Private Sub Workbook_Open()
> Const sPWORD As String = "drowssap"
> Dim aws as Worksheet
> Dim ws As Worksheet
> Application.ScreenUpdating = False
> set aws = ThisWorkbook.Activesheet
> For Each ws In ThisWorkbook.Worksheets
> With ws
> .Activate
> .Unprotect Password:=sPWORD
> .Range("B16:D45").Locked = True
> .Range("N16:N45").Locked = False
> .Protect Password:=sPWORD
> End With
> Next ws
> aws.Activate
> Application.ScreenUpdating = True
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
> "Peter" <[email protected]> wrote in message
> news:[email protected]...
> > Hello JE,
> >
> > Thanks for the info...
> >
> > I have tried the code you suggested, but to no avail....Yet.
> >
> > When I reopen the worksheet saved when exiting, i get a Run TIme Error
> 1004
> > - Unable to set the locked property of the range class error, and the
> > breakpoint rests on the line .Range("B16:D45").Locked = True
> >
> > I have already added the "Trust Access To Visual Basic Project" as it
> > suggested..still no luck.
> >
> > Any suggestions as to what I may have done wrong?
> >
> > Regards
> > Peter
> >
> >
> >
> >
> > "JE McGimpsey" wrote:
> >
> > > One way:
> > >
> > > Const sPWORD As String = "drowssap"
> > > Dim ws As Worksheet
> > > For Each ws In Worksheets
> > > With ws
> > > .Unprotect Password:=sPWORD
> > > .Range("B16:D45").Locked = True
> > > .Range("N16:N45").Locked = False
> > > .Protect Password:=sPWORD
> > > End With
> > > Next ws
> > >
> > > In article <[email protected]>,
> > > "Peter" <[email protected]> wrote:
> > >
> > > > I am trying to get my sheets to work as follows:
> > > >
> > > > Unprotect the worksheet (with password)
> > > >
> > > > Protect cell range B16,D45
> > > >
> > > > Unprotect cell range N16,N45
> > > >
> > > > Protect sheet (with password)
> > > >
> > > > Then move to the next sheet in the Workbook and repeat the process.
> > > > There are 20+ Worksheets in the Workbook.
> > >
>
>
>

Try it in a new workbook. The code works fine for me.

--
Regards,
Tom Ogilvy

"Peter" <[email protected]> wrote in message
news:[email protected]...
> Hello Tom,
> Thanks for the code. It's doing the exact same thing though.
> I have removed all other macro's from my workbook, and it's still the
same.
>
> Any idea's why are welcome..
>
> Regards
> Peter
>
>
> "Tom Ogilvy" wrote:
>
> > Assuming you are doing this in the Workbook_Open event, Try
> >
> > Private Sub Workbook_Open()
> > Const sPWORD As String = "drowssap"
> > Dim aws as Worksheet
> > Dim ws As Worksheet
> > Application.ScreenUpdating = False
> > set aws = ThisWorkbook.Activesheet
> > For Each ws In ThisWorkbook.Worksheets
> > With ws
> > .Activate
> > .Unprotect Password:=sPWORD
> > .Range("B16:D45").Locked = True
> > .Range("N16:N45").Locked = False
> > .Protect Password:=sPWORD
> > End With
> > Next ws
> > aws.Activate
> > Application.ScreenUpdating = True
> > End Sub
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Peter" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hello JE,
> > >
> > > Thanks for the info...
> > >
> > > I have tried the code you suggested, but to no avail....Yet.
> > >
> > > When I reopen the worksheet saved when exiting, i get a Run TIme Error
> > 1004
> > > - Unable to set the locked property of the range class error, and the
> > > breakpoint rests on the line .Range("B16:D45").Locked = True
> > >
> > > I have already added the "Trust Access To Visual Basic Project" as it
> > > suggested..still no luck.
> > >
> > > Any suggestions as to what I may have done wrong?
> > >
> > > Regards
> > > Peter
> > >
> > >
> > >
> > >
> > > "JE McGimpsey" wrote:
> > >
> > > > One way:
> > > >
> > > > Const sPWORD As String = "drowssap"
> > > > Dim ws As Worksheet
> > > > For Each ws In Worksheets
> > > > With ws
> > > > .Unprotect Password:=sPWORD
> > > > .Range("B16:D45").Locked = True
> > > > .Range("N16:N45").Locked = False
> > > > .Protect Password:=sPWORD
> > > > End With
> > > > Next ws
> > > >
> > > > In article <[email protected]>,
> > > > "Peter" <[email protected]> wrote:
> > > >
> > > > > I am trying to get my sheets to work as follows:
> > > > >
> > > > > Unprotect the worksheet (with password)
> > > > >
> > > > > Protect cell range B16,D45
> > > > >
> > > > > Unprotect cell range N16,N45
> > > > >
> > > > > Protect sheet (with password)
> > > > >
> > > > > Then move to the next sheet in the Workbook and repeat the
process.
> > > > > There are 20+ Worksheets in the Workbook.
> > > >
> >
> >
> >