Locking cells, Excel 2003 SP3
 

Hi
I have Excel 2003 SP3 and I have what I thought was correct code when
investigating other peoples issues. However at the end of the loop, the whole
sheet is protected, not just the cells. How do I start from an unprotected
sheet, lock some cells only, and keep the remainder of the sheet protected?
(I have tried with unprotect and protect out of the loop
Thanks

'rstData is a dataset of info which has a column that has an Excel Cell
name such as C10, B23, F16
Set rstData = oDatabase.RecordsetData
With rstData
.MoveFirst
Do While Not .EOF
ActiveWorkbook.ActiveSheet.Unprotect Password:="MyPwd"
ActiveWorkbook.ActiveSheet.Range(rstData("ExcelCel lPos")).Locked
= True
ActiveWorkbook.ActiveSheet.Protect Password:="MyPwd"
.MoveNext
Loop
End With

Locking cells, Excel 2003 SP3
 

My understanding of it is that the sheet is first protected with all cells
locked. The specified cells must then be unlocked for modification of
content. The remainder of the sheet is still protected.


"thebison" wrote in message
...
Hi
I have Excel 2003 SP3 and I have what I thought was correct code when
investigating other peoples issues. However at the end of the loop, the
whole
sheet is protected, not just the cells. How do I start from an unprotected
sheet, lock some cells only, and keep the remainder of the sheet
protected?
(I have tried with unprotect and protect out of the loop
Thanks

'rstData is a dataset of info which has a column that has an Excel Cell
name such as C10, B23, F16
Set rstData = oDatabase.RecordsetData
With rstData
.MoveFirst
Do While Not .EOF
ActiveWorkbook.ActiveSheet.Unprotect Password:="MyPwd"

ActiveWorkbook.ActiveSheet.Range(rstData("ExcelCel lPos")).Locked
= True
ActiveWorkbook.ActiveSheet.Protect Password:="MyPwd"
.MoveNext
Loop
End With

Locking cells, Excel 2003 SP3
 

your logic needs to be reversed.

First you Unprotect the cells that you're ok with others changing.
Next you protect the sheet. only those unprotected cells can now be edited.

"thebison" wrote in message
...
Hi
I have Excel 2003 SP3 and I have what I thought was correct code when
investigating other peoples issues. However at the end of the loop, the
whole
sheet is protected, not just the cells. How do I start from an unprotected
sheet, lock some cells only, and keep the remainder of the sheet
protected?
(I have tried with unprotect and protect out of the loop
Thanks

'rstData is a dataset of info which has a column that has an Excel Cell
name such as C10, B23, F16
Set rstData = oDatabase.RecordsetData
With rstData
.MoveFirst
Do While Not .EOF
ActiveWorkbook.ActiveSheet.Unprotect Password:="MyPwd"

ActiveWorkbook.ActiveSheet.Range(rstData("ExcelCel lPos")).Locked
= True
ActiveWorkbook.ActiveSheet.Protect Password:="MyPwd"
.MoveNext
Loop
End With

Locking cells, Excel 2003 SP3
 

Many thanks to you both, I only have a few cells to lock and a lot of cells
that are unlocked but okay, understand. Many thanks

"JLGWhiz" wrote:

My understanding of it is that the sheet is first protected with all cells
locked. The specified cells must then be unlocked for modification of
content. The remainder of the sheet is still protected.


"thebison" wrote in message
...
Hi
I have Excel 2003 SP3 and I have what I thought was correct code when
investigating other peoples issues. However at the end of the loop, the
whole
sheet is protected, not just the cells. How do I start from an unprotected
sheet, lock some cells only, and keep the remainder of the sheet
protected?
(I have tried with unprotect and protect out of the loop
Thanks

'rstData is a dataset of info which has a column that has an Excel Cell
name such as C10, B23, F16
Set rstData = oDatabase.RecordsetData
With rstData
.MoveFirst
Do While Not .EOF
ActiveWorkbook.ActiveSheet.Unprotect Password:="MyPwd"

ActiveWorkbook.ActiveSheet.Range(rstData("ExcelCel lPos")).Locked
= True
ActiveWorkbook.ActiveSheet.Protect Password:="MyPwd"
.MoveNext
Loop
End With

Locking cells, Excel 2003 SP3
 

Try something like this

ActiveWorkbook.ActiveSheet.Unprotect Password:="MyPwd"
Set rstData = oDatabase.RecordsetData
for each MyRow in RstData.rows
MyRow.entireRow.Locked
next Myrow
ActiveWorkbook.ActiveSheet.Protect Password:="MyPwd"


"thebison" wrote:

Many thanks to you both, I only have a few cells to lock and a lot of cells
that are unlocked but okay, understand. Many thanks

"JLGWhiz" wrote:

My understanding of it is that the sheet is first protected with all cells
locked. The specified cells must then be unlocked for modification of
content. The remainder of the sheet is still protected.


"thebison" wrote in message
...
Hi
I have Excel 2003 SP3 and I have what I thought was correct code when
investigating other peoples issues. However at the end of the loop, the
whole
sheet is protected, not just the cells. How do I start from an unprotected
sheet, lock some cells only, and keep the remainder of the sheet
protected?
(I have tried with unprotect and protect out of the loop
Thanks

'rstData is a dataset of info which has a column that has an Excel Cell
name such as C10, B23, F16
Set rstData = oDatabase.RecordsetData
With rstData
.MoveFirst
Do While Not .EOF
ActiveWorkbook.ActiveSheet.Unprotect Password:="MyPwd"

ActiveWorkbook.ActiveSheet.Range(rstData("ExcelCel lPos")).Locked
= True
ActiveWorkbook.ActiveSheet.Protect Password:="MyPwd"
.MoveNext
Loop
End With