when a new cell is clicked in

breilly00

Board Regular
Joined
Sep 15, 2008
Messages
53
Office Version
  1. 365
Platform
  1. Windows
I am creating a score card for the billards team and want to display a running score while a player is shooting. The process goes as follows
Special K's turn and on his first shot he scores 2 points-the score keeper enters a '2' in the player score column, focus stays on his player score cell (via VBA code)
a number 2 is entered in a cell to the right of the score card (say H1 for example)
Special K shoots again and score 3 points - a '3' is displayed in H2.
On special K's next shot he misses and does not score any points.
----------------------
the score keeper clicks on the playerscore cell for the next player (Banker)
At this time the "2" and "3" should be erased and now Banker's score will start to be shown in H1, H2, etc.

The requirement is to display a running score in column H for each player as their score is entered in their playerscore cell. when they miss, their running score is erased and the next players running score is displayed until they miss and then erase.

It seems to come down to when active cell changes (because I am keeping focus in the cell) or just recognizing that the user has clicked in a different cell but I've looked at a column change, current(active cell change) and cannot get the VBA to work.
How should I go about getting this to work?



Left TeamRight Team
NamePlayer ScoreNamePlayer Score
Special KBanker
GaryBruce
DougGary
RichieMike
Team ScoreTeam Score
Points 2 GoPoints 2 Go
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
What are the column letters for the data you posted? Should "Right Team" be moved one cell to the right? It might be easier to see how your sheet is set up if you use the XL2BB add-in (icon in the menu) to display a screen shot of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.
 
Upvote 0
sorry, did not know about this feature
V1-ok.xlsm
ABCDE
1Left TeamRight Team
2NamePlayer ScoreNamePlayer Score
3BankerBruce
4TomMike
5DougRichie
6Special KGary
7Team ScoreTeam Score
8Points 2 GoPoints 2 Go
Sheet1
Cells with Data Validation
CellAllowCriteria
A3:A6List=ScoreCard!$A$2:$A$9
D3:D6List=ScoreCard!$A$2:$A$9
 
Upvote 0
Copy and paste this code into the worksheet code module. Do the following: right click the tab name for your Sheet1 and click 'View Code'. Paste the code into the empty code window that opens up. Close the code window to return to your sheet. Enter a value in the score column and press the RETURN key.
Code:
Dim oldVal As Long
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("B3:B6,E3:E6")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    oldVal = Range("AB1")
    Range("AA1") = ActiveCell.Row
    Range("AB1") = ActiveCell.Column
    If ActiveCell.Row <> Range("AA1") Or ActiveCell.Column <> oldVal Then
        Range("H2", Range("H" & Rows.Count).End(xlUp)).ClearContents
    End If
    Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B3:B6,E3:E6")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Select Case Target.Column
        Case Is = 2, 5
            Cells(Rows.Count, "H").End(xlUp).Offset(1) = Target
            Target.Select
    End Select
    Application.EnableEvents = True
End Sub
 
Upvote 0
Oh My !!!!!
i just asked for a little help and mumps provided me with a whole 'set' of code. He is great. The delay in the thanks is because I was using his code to go back to google and find some videos/other explains to understand his code. So simple and straightforward are his writings. But, I guess when someone goes above and beyond to help jr. members is what makes Mr. Excel what it is. Eh.

TY again Mumps for your help. Please stay safe in this crisis time.
 
Upvote 0
You are very welcome and you stay safe as well. :)
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top