Using Protect and Unprotect in VBA 1

[anet]

I want to stop a user from entering information on a worksheet that is being updated through VBA code. I am using ActiveSheet.Unprotect, updating the cells, then using ActiveSheet.Protect at the end of the sub. The problem is that when the sheet is unprotected, the updated cells are somehow being reverted to "unlocked" so that when the Protect event runs the only cells that are protected are those that were not changed in my code.

How can I stop the cell format from changing and why is this happening?

Thanks for any help!

 

[acron]

How are you assigning values to the cells in question ?. Are you using paste. If so, use PasteSpecial :

e.g.

Range("A1&quot.PasteSpecial Paste:=xlValues

You could also use

Range("A1&quot.Locked = True, after you change the value.

A.C

 

[anet]

Thanks. I was actually using:

Cells(MyRow, 1).Copy Sheets("Pay Slip&quot.Range("G1&quot

to copy data from one worksheet to another.

So, I added a loop at the end of my sub as follows:

For Each rCell In Selection
If rCell.Locked = False Then
rCell.Locked = True
End If
Next rCell
Sheets("Pay Slip&quot.Protect

Now it seems to be working fine.
A gold star for you!

 

[xlbo]

I know you've solved this but as another way of looking at the issue, how about this:
ActiveSheet.Protect contents:=True, userinterfaceonly:=True

This will allow you to change the sheet via code but users cannot change it manually - could save a LOT of .protect / .unprotect statements... Rgds
~Geoff~

 

[anet]

Thanks Geoff. I tried that first but couldn't get it to work. Maybe I was putting the code in the wrong place? I had placed it in the Worksheet Activate event, is there a better place?

Annette

 

[anet]

OK - this is getting really frustrating. The code that was working yesterday (if rCell.locked = false then rCell.locked = true) today is not and I didn't change anything! Now I am getting a message that says "unable to set the Locked property of the Range class." I tried leaving out the if statement and just put the following:

Set rRange = Sheets("Pay Slip&quot.Range("A1:G19&quot
For Each rCell In rRange
rCell.Locked = True
Next rCell

Surprise, surprise, it didn't work either.

So I commented out that whole section and tried putting the following in my Workbook Open event:

Sheets("Pay Slip&quot.Protect contents:=True, userinterfaceonly:=True

This protects the worksheet, but will not allow updates even in code.

Does anyone have any ideas?

Annette

PS - I am using Excel 2000

 

[smurf01]

Hi,
try this i am using it in a spreadsheet which only allow user input through forms i have created. and we have no problem at all


Sheets(&quot;Pay Slip&quot.Protect (&quot;<Use your own password>&quot, UserInterfaceOnly:=True

This is set at workbook open and again at workbook close just in case someone has unprotected the sheet for some maintenance on it

Regards
Paul