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

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" <(E-Mail Removed)> wrote in message
news:6cd21db6-a023-4c3e-8b7f-(E-Mail Removed)...
> 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

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

Thanks, Alan

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

Regards,
Peter T

"Alan" <(E-Mail Removed)> wrote in message
news:fdb767c2-67de-4690-adbe-(E-Mail Removed)...
> 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,

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

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

"Alan" <(E-Mail Removed)> wrote in message
news:bed9eb14-def2-493b-84b9-(E-Mail Removed)...
> 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