Running a macro if any data is entered in a range of cells
 

Hi

I am totally new to VBA and really need to do some help!

I am writing an excel worksheet to send to students practising for multi
choice exams. They are supposed to press a button when they start the paper
which will then record the start time in a box. I have written a macro that
does this and protects the cell so that the time cannot then be changed but I
want it to automatically run if they answer any questions without pressing
the start button.

My idea for doing this is to use the worksheet_change function to watch the
cells into which the answers would go (namely C12:F37) and if any of those
are changed run the starttime macro.

Can anyone tell me how to do this or give me some code that would work.

Many Thanks

Jonathan

Running a macro if any data is entered in a range of cells
 

Jonathan,

Here is the worksheet change code that assumes the time is recorded in A1.
You will need to add protection as well

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C12:F37"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Me.Range("A1").Value = "" Then
Me.Range("A1").Value = Now
End If
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.




--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jonathan" wrote in message
...
Hi

I am totally new to VBA and really need to do some help!

I am writing an excel worksheet to send to students practising for multi
choice exams. They are supposed to press a button when they start the
paper
which will then record the start time in a box. I have written a macro
that
does this and protects the cell so that the time cannot then be changed
but I
want it to automatically run if they answer any questions without pressing
the start button.

My idea for doing this is to use the worksheet_change function to watch
the
cells into which the answers would go (namely C12:F37) and if any of those
are changed run the starttime macro.

Can anyone tell me how to do this or give me some code that would work.

Many Thanks

Jonathan

Running a macro if any data is entered in a range of cells
 

Thanks so much for your help Bob,

I have modified your code to add protection and a helpful message box so
it's all working beautifully.

Thanks again

Jonathan

"Jonathan" wrote:

Hi

I am totally new to VBA and really need to do some help!

I am writing an excel worksheet to send to students practising for multi
choice exams. They are supposed to press a button when they start the paper
which will then record the start time in a box. I have written a macro that
does this and protects the cell so that the time cannot then be changed but I
want it to automatically run if they answer any questions without pressing
the start button.

My idea for doing this is to use the worksheet_change function to watch the
cells into which the answers would go (namely C12:F37) and if any of those
are changed run the starttime macro.

Can anyone tell me how to do this or give me some code that would work.

Many Thanks

Jonathan

Running a macro if any data is entered in a range of cells
 

That's great Jonathan. Glad to be of help.

Regards

Bob


"Jonathan" wrote in message
...
Thanks so much for your help Bob,

I have modified your code to add protection and a helpful message box so
it's all working beautifully.

Thanks again

Jonathan

"Jonathan" wrote:

Hi

I am totally new to VBA and really need to do some help!

I am writing an excel worksheet to send to students practising for multi
choice exams. They are supposed to press a button when they start the
paper
which will then record the start time in a box. I have written a macro
that
does this and protects the cell so that the time cannot then be changed
but I
want it to automatically run if they answer any questions without
pressing
the start button.

My idea for doing this is to use the worksheet_change function to watch
the
cells into which the answers would go (namely C12:F37) and if any of
those
are changed run the starttime macro.

Can anyone tell me how to do this or give me some code that would work.

Many Thanks

Jonathan