VBA help, recording the time and location of clicked cells

[pltaine]

Hi guys,

I am new to the whole VBA side of Excel and have hit a wall which requires superior knowledge.

What I am trying to do it create a 'log' sheet so that i can record which cells are clicked and at what time they are clicked. Consider this like a scoreboard for basketball. Everytime the home team score a point, I click on either the 1,2 or 3 cells B1, B2 and B3 respectively depending on how many points they scored. This would then add onto their total.

However after the game, I want to go back and see at what time the points were scored, and how many points were scored with that shot.

I have 2 different sheets, 1 for the clicking the cells to add the scores (which I have already put into the VBA code for the sheet) and another sheet to store the feed data with headings "Time" and "Cell". I can then fill in the rest of the feed grid by using vlookups.

Any ideas on how I can do this?

Note: Because of the nature of what I'm trying to do, the scores are added by clicking on the cells, they are not buttons with macros attached to them.

 

[Vbagreenhorn1]

place this code in the sheet you click:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 2 And Target.Row <= 3 Then
    With Sheets("Blad2")
    Lr = .Range("A" & Rows.Count).End(xlUp).Row + 1
        .Cells(Lr, 1) = Time
        .Cells(Lr, 2) = Target.Address
    End With
End If
End Sub

Change "Blad2" to the sheetname in which you store the feed

If you dont want the $$$ signs then use this one:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 2 And Target.Row <= 3 Then
    With Sheets("Blad2")
    Lr = .Range("A" & Rows.Count).End(xlUp).Row + 1
        .Cells(Lr, 1) = Time
        .Cells(Lr, 2) = Replace(Target.Address, "$", "")
    End With
End If
End Sub

 

[pltaine]

Thanks, this code looks good, however it has come up with Ambiguous name detected: Worksheet_SelectionChange.

Any ideas?

 

[Vbagreenhorn1]

Paste my code in the existing selection change event

 

[pltaine]

I think the issue was I had 2 Selection changes in the same sheet, so I put them together and now it works great! Thanks for the help!