Change cell state with protected sheet

[Chris]

Hi All,

I'm hoping someone will be able to help with this.

I have a worksheet in which users select (double click) on a range of
cells, when they do this it changes the cell and font colour, records
the cell state and produces a report at the end giving them an update
of their score. the spreadsheet is laid out like below:
cell a1 Ques: Do you like work?
Cell b1 Yes
cell c1 No
cell d1 Maybe

The problem is I want to lock the cells so that no-one can delete or
amend the cells apart from the double click. I have tried normal
password protection to no avail (as all changes are locked) and have
also added in the following code:

ActiveSheet.Protect UserInterfaceOnly:=True

This almost does what I need except you can still hit either delete or
f2. Does anyone know of a way to limit a cell to only button
clicks?????

Any help / advice would be greatly appreciated.

Thanks in advance.

Chris

 

[GS]

Chris explained on 6/3/2011 :

Hi All,

I'm hoping someone will be able to help with this.

I have a worksheet in which users select (double click) on a range of
cells, when they do this it changes the cell and font colour, records
the cell state and produces a report at the end giving them an update
of their score. the spreadsheet is laid out like below:
cell a1 Ques: Do you like work?
Cell b1 Yes
cell c1 No
cell d1 Maybe

The problem is I want to lock the cells so that no-one can delete or
amend the cells apart from the double click. I have tried normal
password protection to no avail (as all changes are locked) and have
also added in the following code:

ActiveSheet.Protect UserInterfaceOnly:=True

This almost does what I need except you can still hit either delete or
f2. Does anyone know of a way to limit a cell to only button
clicks?????

Any help / advice would be greatly appreciated.

Thanks in advance.

Chris

On a protected sheet, if the cell is editable via F2 OR its contents
can be deleted then the cell is not 'locked'. Make sure the cell you
want users to access ARE locked AND when protection is applied specify
they can select locked cells.

 

[GS]

I forgot to mention that you might want to use another mechanism or
methodology for user selection since the double-click event also cause
users to enter EditMode on the cell they double-clicked.

Perhaps they could make all selections first then click a button on the
sheet that generates the report. You can manage what cells they select
using the Intersect() function to make sure they select within the
correct range, AND only 1 answer for each Q. This will allow you to
change Font.Color and [progressively] add the cell value to your report
output string.

If you insist to use double-click:
Optionally, you can use Data Validation to restrict editing without
displaying a cell dropdown or alerts.

 

[GS]

GS used his keyboard to write :

If you insist to use double-click:
Optionally, you can use Data Validation to restrict editing without
displaying a cell dropdown or alerts.

I meant to add...

In the Worksheet_BeforeDoubleClick event, after you've determined where
the user double-clicked you can set 'Cancel' = True to avoid entering
EditMode and thus any alerts that may display as a result of the cell
being locked.

Example code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
If Not Intersect(Target, Range("Answers")) Is Nothing Then
Target.Font.ColorIndex = 3 '//red
Cancel = True '//dismiss alerts and disable EditMode
End If
End Sub