Easy one: On Open selected cells in a worksheet are disabled.

[WAP]

Hi,
If i want to disable particular cells on a worksheet that
contain values so that they can't be changed when it
opens what is the correct code. i have tried the
following but get error 91 or subscript out of range.

Option Explicit
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim xlsheet As Object


Sub auto_open()



Set xlsheet = Worksheets("sheet1")
Set rng = xlsheet.Range("b5")
rng.Enabled = False
Set rng1 = xlsheet.Range("b7:c7")
rng1.Enabled = False
Set rng2 = xlsheet.Range("a11:g23")
rng2.Enabled = False

End Sub

Thanks
WAP

 

[Chip Pearson]

Ranges don't have an Enabled property, so the code

rng.Enabled = False

will fail. Use the Locked property and then protect the
worksheet.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[WAP]

Thanks,
I think I've got it correct but I get error saying that
the subscript is out of range in line

Set xlsheet = Worksheets("sheet1")

of corrected code

Sub auto_open()

Set xlsheet = Worksheets("sheet1")
Set rng = xlsheet.Range("b5")
rng.Locked = True
Set rng1 = xlsheet.Range("b7:c7")
rng1.Locked = True
Set rng2 = xlsheet.Range("a11:g23")
rng2.Locked = True

End Sub

ummmm??????
Just a wee bit lost here - be gentle please.
Regards
WAP

 

[WAP]

Chip,
If I use the following code,

Sub auto_open()

Worksheets(1).Range("b3:b6").Locked = False
Worksheets(1).Range("f6:f8").Locked = False

Worksheets(1).Protect

End Sub

Nothing happens,
I can still change cells at will.
The cells that are to be unlocked are data entry cells,
the cells that are protected are reference data and cells
that contain formulii.
Regards
WAP

 

[Chip Pearson]

You need to set the Locked property to True, not False.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Chip Pearson]

Do you in fact have a sheet named "Sheet1"?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com