Using VBA to Unlock Cells
 

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, D37:D39,
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

Using VBA to Unlock Cells
 

Hi Marcia

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

HTH. Best wishes Harald



"Office User" skrev i melding
...
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,
D37:D39,
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

Using VBA to Unlock Cells
 

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

Using VBA to Unlock Cells
 

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, D37:D39,
E40:E41, F42].Locked = False
.Protect UserInterfaceOnly:=True 'Protect worksheet
End With
End Sub

Thanks for any input.
Marcia

"Paul Martin" wrote:

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

Using VBA to Unlock Cells
 

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,
D37:D39,
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

"Office User" wrote in message
...
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,
D37:D39,
E40:E41, F42].Locked = False
.Protect UserInterfaceOnly:=True 'Protect worksheet
End With
End Sub

Thanks for any input.
Marcia

"Paul Martin" wrote:

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

Using VBA to Unlock Cells
 

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

"Tom Ogilvy" wrote:

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,
D37:D39,
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

"Office User" wrote in message
...
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,
D37:D39,
E40:E41, F42].Locked = False
.Protect UserInterfaceOnly:=True 'Protect worksheet
End With
End Sub

Thanks for any input.
Marcia

"Paul Martin" wrote:

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

Using VBA to Unlock Cells
 

And Unprotect before you make the changes

Using VBA to Unlock Cells
 

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

--
Greetings from New Zealand
Bill K


"Paul Martin" wrote in message
oups.com...
And Unprotect before you make the changes