Protect a worksheet but enable data to be added Excel 2003 1

[justagrunt]

Hi,
I am using the following code to disable a worksheet so
that people cannot tamper with data on it, as it is used
in formulas else where on the sheet, but allows them to
enter data in allocated cells which is then worked on to
give a result. The trouble is It doesn’t seem to work.
The cells can that are supposed to be locked out can still be modified.
Option Explicit
Private module

Sub auto_open()
'when the workbook opens the worksheet(1) is protected
'existing data and formulii are not allowed to be changed
'only certain cell can be altered to allow data entry
'all remaining worksheets are hidden

Worksheets(1).Visible = True
Worksheets(1).Activate

ActiveSheet.Unprotect

ActiveSheet.Range("b3:b4").Locked = False 'data entry cells
ActiveSheet.Range("b6").Locked = False ' data entry
ActiveSheet.Range("f7").Locked = False ' variable entry
ActiveSheet.Protect

Worksheets(2).Visible = False
Worksheets(3).Visible = False
Worksheets(4).Visible = False
Worksheets(5).Visible = False
Worksheets(6).Visible = False
Worksheets(7).Visible = False
Worksheets(8).Visible = False


MsgBox "ok to here", vbOKOnly
End Sub
Regards
Bill

 

[cLFlaVA]

Hello again Bill--

Could you elaborate a little more?
You're saying that before this Macro runs, everything is correct. And then, the Macro runs, unprotects the sheet, sets the Locked attribute of 4 cells to False, then Locks the sheet again.

And, after this, what is the problem?

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[justagrunt]

Well - umm - yes,
Its a calulation table for sizing of a machine.
On open the sheet is as it was left by the last user.
The new user can enter data in 3 places and alter a parameter in another. (the unlocked cells)
What i don't want to happen is them to tamper with some cells that use a vlookup dependent on the data input, by just entering a number in that cell.
Also on the sheet is a table for refernce, which I don't want values changed, hence the need to protect the sheet.
In access you can disable a control, so that data updates but the user can't tamper with. Thats what I was hoping to do with excel. Only, after the sheet has opened, I can tamper with the table and overright the vlookup cell etc.
So all I can figure is the method isn't working so the help files didn't give me much joy as to reason.
So I can still alter all cells which I don't want , after the macro runs.
Kind Regards
bill

 

[cLFlaVA]

Silly question, but have you ensured that ALL cells are "Locked"?

Click the Column/Row junction (top-left gray cell) - This should select ALL cells.
Go to Format > Cells...
Click the Protection tab
Make sure the Locked checkbox is CHECKED and BLACK (not gray).
Lock the Worksheet.

Next time you run the Macro, it will unlock the sheet, set those four cells "Locked" property to False, and then re-protect the sheet.

I tested your code, and it works. Not sure what the problem might be, other than those other cells were never locked in the first place.

Maybe you'll want to do this in the macro too - set every cell's Locked property to True, except those four cells.

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[justagrunt]

Arr- Thanks,
Not a silly question - spot on.
Ta,
Bill