Using VBA to Unlock Cells

[Guest]

I have the following code when my workbook opens:
Public Sub Workbook_Open()
ThisWorkbook.Sheets(3).Range("L4").Value = NextSeqNumber
ThisWorkbook.Sheets(3).Range("data1").Value = Date
With Sheet3
'Unlock cells for data entry
[E12:E15, G14, I14, L13:L15, K14:K15, D18:K34, G37:G38, D3739,
E40:E41, F42].Locked = False
.Protect 'Protect worksheet
End With
End Sub

The sequential number and date do work because I had that code working
before adding the code to unlock the cells noted and protect the rest of the
worksheet from being changed.

When I open it, I get the following message:
Run time error 1004 Unable to set the Locked property of the Range class.

This is a template file if that would make any difference. There are some
named cell references in this worksheet but none of them are called "class".
I'm using Excel 2003 SP1 on Windows XP SP2.

Thanks for any insight.
Marcia

 

[Harald Staff]

Hi Marcia

You'll need a
..Unprotect
before the unlocking if the sheet is protected.

HTH. Best wishes Harald

 

[Paul Martin]

Hi Marcia

If I'm applying .Protect to a WorkSheet, I like to use the
UserInterfaceOnly argument like so:

Sheet3.Protect, , , True or
Sheet3.Protect UserInterfaceOnly:=True

That way your worksheet is protected but any macros you write are not
affected by the protection (ie, you can change a value on a worksheet
while it is protected, which a user cannot do)

Regards

Paul Martin
Melbourne, Australia

 

[Guest]

I added the suggestions from Paul and Harald but still getting the same error
message: Run time error 1004 Unable to set the Locked property of the Range
class.

Here's the code now:
Public Sub Workbook_Open()
ThisWorkbook.Sheets(3).Range("L4").Value = NextSeqNumber
ThisWorkbook.Sheets(3).Range("data1").Value = Date
With Sheet3
.Unprotect
Cells.Locked = True
'Unlock cells for data entry
[E12:E15, G14, I14, L13:L15, K14:K15, D18:K34, G37:G38, D3739,
E40:E41, F42].Locked = False
.Protect UserInterfaceOnly:=True 'Protect worksheet
End With
End Sub

Thanks for any input.
Marcia

 

[Tom Ogilvy]

the unqualified Cells pertains to the activesheet which many not be sheet3

Public Sub Workbook_Open()
ThisWorkbook.Sheets(3).Range("L4").Value = NextSeqNumber
ThisWorkbook.Sheets(3).Range("data1").Value = Date
With Sheet3
.Unprotect
.Cells.Locked = True
'Unlock cells for data entry
.[E12:E15, G14, I14, L13:L15, K14:K15, D18:K34, G37:G38,
D3739,
E40:E41, F42].Locked = False
.Protect UserInterfaceOnly:=True 'Protect worksheet
End With
End Sub

You need periods before Cells and [E12:E15, . . .

--
Regards,
Tom Ogilvy

I added the suggestions from Paul and Harald but still getting the same error
message: Run time error 1004 Unable to set the Locked property of the Range
class.

Here's the code now:
Public Sub Workbook_Open()
ThisWorkbook.Sheets(3).Range("L4").Value = NextSeqNumber
ThisWorkbook.Sheets(3).Range("data1").Value = Date
With Sheet3
.Unprotect
Cells.Locked = True
'Unlock cells for data entry
[E12:E15, G14, I14, L13:L15, K14:K15, D18:K34, G37:G38, D3739,
E40:E41, F42].Locked = False
.Protect UserInterfaceOnly:=True 'Protect worksheet
End With
End Sub

Thanks for any input.
Marcia

Hi Marcia

If I'm applying .Protect to a WorkSheet, I like to use the
UserInterfaceOnly argument like so:

Sheet3.Protect, , , True or
Sheet3.Protect UserInterfaceOnly:=True

That way your worksheet is protected but any macros you write are not
affected by the protection (ie, you can change a value on a worksheet
while it is protected, which a user cannot do)

Regards

Paul Martin
Melbourne, Australia

 

[Guest]

Thanks. That should have been obvious to me. Sometimes it's the little
things that will trip you up. :-(
Marcia

the unqualified Cells pertains to the activesheet which many not be sheet3

Public Sub Workbook_Open()
ThisWorkbook.Sheets(3).Range("L4").Value = NextSeqNumber
ThisWorkbook.Sheets(3).Range("data1").Value = Date
With Sheet3
.Unprotect
.Cells.Locked = True
'Unlock cells for data entry
.[E12:E15, G14, I14, L13:L15, K14:K15, D18:K34, G37:G38,
D3739,
E40:E41, F42].Locked = False
.Protect UserInterfaceOnly:=True 'Protect worksheet
End With
End Sub

You need periods before Cells and [E12:E15, . . .

--
Regards,
Tom Ogilvy

I added the suggestions from Paul and Harald but still getting the same error
message: Run time error 1004 Unable to set the Locked property of the Range
class.

Here's the code now:
Public Sub Workbook_Open()
ThisWorkbook.Sheets(3).Range("L4").Value = NextSeqNumber
ThisWorkbook.Sheets(3).Range("data1").Value = Date
With Sheet3
.Unprotect
Cells.Locked = True
'Unlock cells for data entry
[E12:E15, G14, I14, L13:L15, K14:K15, D18:K34, G37:G38, D3739,
E40:E41, F42].Locked = False
.Protect UserInterfaceOnly:=True 'Protect worksheet
End With
End Sub

Thanks for any input.
Marcia

Hi Marcia

If I'm applying .Protect to a WorkSheet, I like to use the
UserInterfaceOnly argument like so:

Sheet3.Protect, , , True or
Sheet3.Protect UserInterfaceOnly:=True

That way your worksheet is protected but any macros you write are not
affected by the protection (ie, you can change a value on a worksheet
while it is protected, which a user cannot do)

Regards

Paul Martin
Melbourne, Australia

 

[Paul Martin]

And Unprotect before you make the changes

 

[Bill Kuunders]

Have read the answers so far.
Just can't understand why you do not just unlock the cells in...........
<format><cells><"protection" tab>