Is there any way that I can write a macro or code for a quiz sheet I
have made.
I want my pupils to be able to type into the cell but would like it
lock the cell after they have typed something in so effectively I can
use it as a test but the first answer is the one that counts.
Somebody suggested that I rightclick on the workbook tab and then use
the view code function, but I know nothing about computer code and
would not know what to write in the boxes
Any ideas would be greatly appreciated.
Thanks
Dave


--
HuckinD

I'm confident you can do this by setting a 'protection' value using the
programming language called VBA. However, it's beyond my rather paltry
skills. You should address the question to the 'programming' section on this
web page, where the gurus who know that stuff live.

"HuckinD" wrote:


Is there any way that I can write a macro or code for a quiz sheet I
have made.
I want my pupils to be able to type into the cell but would like it
lock the cell after they have typed something in so effectively I can
use it as a test but the first answer is the one that counts.
Somebody suggested that I rightclick on the workbook tab and then use
the view code function, but I know nothing about computer code and
would not know what to write in the boxes
Any ideas would be greatly appreciated.
Thanks
Dave


--
HuckinD

On Sun, 16 Oct 2005 21:16:48 +0000, HuckinD
wrote:


Is there any way that I can write a macro or code for a quiz sheet I
have made.
I want my pupils to be able to type into the cell but would like it
lock the cell after they have typed something in so effectively I can
use it as a test but the first answer is the one that counts.
Somebody suggested that I rightclick on the workbook tab and then use
the view code function, but I know nothing about computer code and
would not know what to write in the boxes
Any ideas would be greatly appreciated.
Thanks
Dave


How foolproof would you want this to be? To set protection you need to
set a password. If you want to have a macro temporarily switch off
protection whilst the user enters a value, I guess you're going to
have to have the password stored in the macro, and anyone who knew how
to find their way around VBA could discover the password. That said
you could sort of hide the password in a complex algorithm rather than
a straightforward piece of text.

If this rudimentary security is acceptable, I think the approach I'd
adopt would be to name all the cells where you want the answers. e.g.
"Ans1", "Ans2" etc. and have the macro store in the comment behind the
cell a flag to show it had been accessed.

The macro would respond to the SheetChange event when an answer cell
is selected, and first test to see if the comment flag has been set.
If not then allow an answer and set the flag.

If this is the sort of thing that you want, post back and I'll try and
suggest some code. How many questions would you want to handle and are
there any other design features required?

Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Here is one way.

Add this code to the worksheet in question (change the range in
RANGE_MONITOR to your range)

Private Sub Worksheet_Change(ByVal Target As Range)
Const RANGE_MONITOR As String = "H1:H10"
Dim sPW As String
On Error GoTo ws_exit:
sPW = Evaluate(ThisWorkbook.Names("___pw").RefersTo)
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(RANGE_MONITOR)) Is Nothing Then
With Target
Me.Unprotect Password:=sPW
.Locked = True
Me.Protect Password:=sPW
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

Then run this code on the worksheet, but delete it afterwards, don';t leave
it around

Sub SetupSheet()
ActiveSheet.Cells.Locked = False
ActiveWorkbook.Names.Add Name:="___pw", RefersTo:="ABCD"
ActiveWorkbook.Names("___pw").Visible = False
ActiveSheet.Protect Password:="ABCD"
End Sub

Change the password to suit.


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"HuckinD" wrote in message
...

Is there any way that I can write a macro or code for a quiz sheet I
have made.
I want my pupils to be able to type into the cell but would like it
lock the cell after they have typed something in so effectively I can
use it as a test but the first answer is the one that counts.
Somebody suggested that I rightclick on the workbook tab and then use
the view code function, but I know nothing about computer code and
would not know what to write in the boxes
Any ideas would be greatly appreciated.
Thanks
Dave


--
HuckinD