enable selection - protection

[walshy]

Hi wonder if anyone could help me, I have created a
worksheet for users to input in certain cells... Im doing
the finishing touches but the problem is as follows :

I go into VB editor, select the worksheet, then go into
the properties selection underneath, select
EnableSelection = 1 - xlunlockedcells (So no other cell on
the workesheet can be selected only ones user can type in,
and this works fine BUT... when I save exit and go back
into the file it has reset the enableselection to 0
xlnorestrictions ??? Can anyone please help me on how to
keep the settings on this ???

Many Thanks

 

[Guest]

Walshy,

Why don't you just select the whole worksheet, right click, format, protection and check box locked. Then select the cells you allow the users to edit and again format but this time unselect the locked box in protection.

Finally tools, protect sheet using a password.

Harry

 

[Ron de Bruin]

This setting is not saved with the file
You must add code to the workbook open event to set it
every time you open the workbook

If you copy this event in the Thisworkbook module
http://www.mcgimpsey.com/excel/modules.html

It will run automatic when you open the workbook and
Protect each sheet in the workbook and set the EnableSelection

Test it on a test workbook

Private Sub Workbook_Open()
Dim Sh As Worksheet
Application.ScreenUpdating = False
For Each Sh In ThisWorkbook.Worksheets
Sh.Select
Sh.Protect userinterfaceonly:=True
Sh.EnableSelection = xlUnlockedCells
Next
Sheets(1).Select
Application.ScreenUpdating = True
End Sub

 

[Guest]

Thanks, I have already done this but you are still able to
select the cell wether its locked or not... I am looking
to only be able to Select unlocked cells... The only way I
can think of is through vbeditor enable selection...?

-----Original Message-----
Walshy,

Why don't you just select the whole worksheet, right

click, format, protection and check box locked. Then
select the cells you allow the users to edit and again
format but this time unselect the locked box in protection.

 

[Guest]

This is perfect thank you, just one thing, how would I
select certain sheet to do this on rather than all ?

-----Original Message-----
This setting is not saved with the file
You must add code to the workbook open event to set it
every time you open the workbook

If you copy this event in the Thisworkbook module
http://www.mcgimpsey.com/excel/modules.html

It will run automatic when you open the workbook and
Protect each sheet in the workbook and set the EnableSelection

Test it on a test workbook

Private Sub Workbook_Open()
Dim Sh As Worksheet
Application.ScreenUpdating = False
For Each Sh In ThisWorkbook.Worksheets
Sh.Select
Sh.Protect userinterfaceonly:=True
Sh.EnableSelection = xlUnlockedCells
Next
Sheets(1).Select
Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"walshy" <[email protected]> wrote in

message news:[email protected]...

 

[Ron de Bruin]

Try this

Private Sub Workbook_Open()
Application.ScreenUpdating = False
With Sheets("Sheet2")
.Select
.Protect userinterfaceonly:=True
.EnableSelection = xlUnlockedCells
End With
Sheets(1).Select
Application.ScreenUpdating = True
End Sub