James, I don't know that you're doing anything wrong. Someone who uses 2007
more than I do will hopefully come along and tell us differently.
But that never worked for me in 2003 and still doesn't work for me in 2007
while the sheet is protected. Not unless everything affected (i.e. all the
cells/rows or entire worksheet) is unlocked, which pretty much does away with
the value added by protecting the sheet.
I think most people's solution has been to set up a couple of macros:
1 to unprotect the sheet with a minimum of fuss, along with a second one to
put it back into protected state.
You can create those macros by recording a macro as you go through each
operation (start recording, set protection, stop recording for 1st macro,
start recording again, unprotect, stop recording for 2nd macro).
If your sheet has a password assigned to it, you'll need to edit the macros
recorded to provide the password by adding:
, password:="mysheetpassword"
to the end of the commands that do the protecting/unprotecting. Notice that
the addition starts with a comma and a space. Naturally, change
"mysheetpassword" to whatever your sheet's real password is. If you don't
use a password, don't bother with adding the added command parameter.
Hope this helps some, and I do hope someone comes along with a better answer
for you.
