Impossible programming!?

[Guest]

Hi, I am a teacher and I have a spreadsheet to monitor behaviour. I would
like it to go one step further though... but it may be impossible!?

I currently have a sheet with my pupils names down one side. In the cells
next to the names I am using a double-click event to ut a x, xx, or 15 into
the cells to denote level of warnings received. at the very top of this
column I have a doubleclick event to put in the current date.

Now.... What I would like is for when a pupils reaches 15 for it to be
pasted into a new sheet where I can note when the detention is due. I would
then like this to happen for other pupils that receive "15" and for it to
make a list as it were with dates they received the 15.

Is this possible? If so.... Thank you thank you thank you!!!

PS. I have an example of the spreadsheet if needed.

 

[Guest]

Sure that is possible. Can you post the code that you are using and let me
know which sheet you would like the info to land in.

I assume that the correct flow of the program would be that when you double
click on the cell and it turns to 15, you would likt the name of that pupil,
and the current date to be populated in the next available row in the other
sheet???

 

[Guest]

Hi Jim...

That is spot on about it landing on a new row in another sheet. Below is the
current code I have for the doubleclick events.

Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)
Cancel = True
If Target.Row = 1 Then
Target.Value = Date
Else
Select Case Target.Value
Case Empty
Target.Value = "x"
Case "x"
Target.Value = "xx"
Case "xx"
Target.Value = "15"
End Select
End If
End Sub

Thanks ever so much for your help. The code needs to go into a sheet called
"SIOs".

Moving on slightly further... I may well want it to go to another workbook
at some future point, again creating a list, but with an extra column saying
what workbook it came from?

Thanks.

Paul.

 

[Guest]

PS....

I use a different column every lesson. Each column has that days date on, so
theoretically a pupil could have a row of 15s beside their name.

 

[Guest]

I can't imagine any student getting an entire row of 15's <g>...
Give this a try...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim rng As Range

Cancel = True
If Target.Row = 1 Then
Target.Value = Date
Else
Select Case Target.Value
Case Empty
Target.Value = "x"
Case "x"
Target.Value = "xx"
Case "xx"
Target.Value = 15
Set rng = Sheets("SIOs").Cells(Rows.Count, _
"A").End(xlUp).Offset(1, 0)
rng.Value = Cells(Target.Row, "A").Value
rng.Offset(0, 1).Value = Cells(1, Target.Column).Value
End Select
End If
End Sub

 

[Guest]

Hi Jim...

Thanks for the code. It put the date in a new row on the SIOs sheet but no
names unfortunately!!

Paul.

 

[Guest]

Oh... and it also only worked for one 15. I tried other pupils but nothing
else happened.

 

[Guest]

SORRY... Totally works. I had my names in column B and your think was looking
at column A. All sorted. Brilliant!!! How could I adapt it to send to another
workbook or copy other data from other columns? What bit of code should I be
looking at?