2003 & 2007 Differences in Locking Cells Syntax

[Alan]

The code below runs fine in Excel 2007, but 2003 does not like it.
I`m not sure why. It gives me a runtime error and says it is unable
to set the Locked property of the range class.

I tried commenting out the first line (WS.Cells.Locked = False) and
changing the second statement to"

WS.Range(Cells(row,col),Cells(row,col)).Locked = True

However, this did not help.

Does anyone see what I am doing wrong? Thanks, Alan

Sub DisableCellInput(WS As Worksheet, row As Long, col As Long)
'
' This subroutine locks and greys out the cell
'
WS.Cells.Locked = False
' Lock cell for input
WS.Cells(row, col).Locked = True
' Fill cell with light grey
WS.Cells(row, col).Interior.ColorIndex = 15
' Turn text into a slightly darker grey
WS.Cells(row, col).Font.ColorIndex = 48
' Protect the worksheet
WS.Protect UserInterfaceOnly:=True
End Sub

 

[Peter T]

Not sure about your particular issue but some observations.

- As written, previously unlocked cells get unlocked, then just one new cell
gets locked (iow previously locked cells get unlocked)
- The routine would only work one time, when the sheet was originally
unprotected (need to unprotect each time).
- There's no password

Try the following, but check it carefully as it might not be quite as you
want

Sub DisableCellInput(ws As Worksheet, row As Long, col As Long)
' This subroutine locks and greys out the cell
'
Const cPW As String = "Password" ' <<< CHANGE
' ' ws.Cells.Locked = False
ws.Unprotect cPW
' Lock cell for input
ws.Cells(row, col).Locked = True
' Fill cell with light grey
ws.Cells(row, col).Interior.ColorIndex = 15
' Turn text into a slightly darker grey
ws.Cells(row, col).Font.ColorIndex = 48
' Protect the worksheet
ws.Protect Password:=cPW, UserInterfaceOnly:=True
End Sub

Regards,
Peter T

 

[Alan]

Is anyone aware of differences in Excel 2003 and 2007 in this area? I
have not been able to find any documented.

Thanks, Alan

 

[Peter T]

Did you try the amended routine I suggested in both Excel 2003 and 2007?

Regards,
Peter T

 

[Alan]

Peter,

You can protect a sheet without a password. However, the following
code worked for me. The reason for the four subs/functions is that I
reused 2 subs from some earlier code.

Alan

Sub DisableCellInput(WS As Worksheet, row As Long, col As Long)
'
' This subroutine locks and greys out the cell
'
Debug.Print "Disabling . . . in WS " & WS.Name
WS.Cells(row, col).Select
Call ProtectSelectedCells
End Sub

Sub EnableCellInput(WS As Worksheet, row As Long, col As Long)
'
' This subroutine unlocks a cell for input and changes its colors
' to black text on white background, to indicate that
'
' For debug only
Debug.Print "Enabling Cell Input . . . "
WS.Cells(row, col).Select
Call UnprotectSelectedCells
End Sub

Sub ProtectSelectedCells()
'
Dim CellsToLock As Range
Set CellsToLock = Selection
ActiveSheet.Unprotect
CellsToLock.Locked = True
' Fill cell with light grey
CellsToLock.Interior.ColorIndex = 15
' Turn text into a slightly darker grey
CellsToLock.Font.ColorIndex = 48
ActiveSheet.Protect
End Sub

Sub UnprotectSelectedCells()
'
Dim CellsToUnlock As Range
Set CellsToUnlock = Selection
ActiveSheet.Unprotect
CellsToUnlock.Locked = False
' Fill cell with white
CellsToUnlock.Interior.Color = vbWhite
' Turn text to black
CellsToUnlock.Font.Color = vbBlack
ActiveSheet.Protect
End Sub

 

[Peter T]

Yes I'm well aware you can protect a sheet without a password. Your original
code worked fine, at least in the sense it did as much as it was allowed to
do, and no difference with Excel 2007. Hence the alternative I suggested.
I'm a bit confused, are you saying everything is now working OK or is there
still a problem

Regards,
Peter T