Count Clicks in excel
Can some one recommend any free progr开发者_JAVA百科am which counts the number of clicks Clicked inside a cell.
For Example Imagine something like Spreadsheet I click on A1 cell the value shows 1 Then I click A1 cell again the value shows 2 and so on If I click A3 cell somewhere in between the click count on Cell A3 shows 1 and so on
If something like this can be achieved as a macro with in excel (2003 please) please suggest or any other free program that you might be aware about, please do let me know. I appreciate all your help and thank you in advance.
Excel doesn't have a worksheet event for the mouse left click.
It does have an event for 'SelectionChange' and this can be combined with an API call to check if the left mouse button was clicked.
This code needs to go into the Sheet Object in the Project Explorer area for the worksheet that you are targeting.
Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
Private Const MOUSEEVENTF_LEFTDOWN = &H2
Private Const MOUSEEVENTF_LEFTUP = &H4
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Key As Integer
If Target.Count > 1 Then Exit Sub
''//If multiple cells selected with left click and drag
''// then take no action
Key = GetKeyState(MOUSEEVENTF_LEFTDOWN)
If Key And 1 Then
If IsNumeric(Target.Value) Then
Target.Value = Target.Value + 1
''//Check to see if cell contains a number, before
''// trying to increment it
Application.EnableEvents = False
Target.Resize(1, 2).Select
Application.EnableEvents = True
''//Resize the selection, so that if the cell is clicked
''// for a second time, the selection change event is fired again
End If
End If
End Sub
Although this code works, it can increment a cell value even if the user has not left clicked their mouse.
I would recommend using the 'BeforeDoubleClick' event instead if possible. This is built into Excel and is more reliable than the code above.
In order to increment a cell value, the user would need to double click on the cell.
This code needs to go into the Sheet Object in the Project Explorer area for the worksheet that you are targeting.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If IsNumeric(Target.Value) Then
Target.Value = Target.Value + 1
''//Check to see if cell contains a number, before
''// trying to increment it
Application.EnableEvents = False
Target.Resize(1, 2).Select
Application.EnableEvents = True
''//Resize the selection, so that if the cell is clicked
''// for a second time, the selection change event is fired again
Cancel = True
''//Stop the cell going into edit mode
End If
End Sub
精彩评论