calculating clicks

[tuggers1975]

Hi all, its been literally years since I wrote any kind of macro and have completely forgeotten everything I ever learned so please be gentle with me lol

I initially need to know how to create a numerical entry in a cell based on how many times it is clicked on.
The sheet will be for a call log and will need to show the total number of calls against a time incoming, and the total number of outbound calls.
I want to be able to click on a cell and a "1" be entered, then if I click on it again a "2" is entered, then "3" and so on............
Is this possible?

 

[Fluff]

How about

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Target.Address(0, 0) = "A1" Then
      Target.Value = Target.Value + 1
      Cancel = True
   End If
End Sub

This needs to go in the relevant sheet module, then whenever you double-click on A1 the value will increase.

 

[tuggers1975]

Brilliant thank you......

How do I adapt this so it works on any cell within a range?

 

[Peter_SSs]

How do I adapt this so it works on any cell within a range?

Adjust the range to suit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Not Intersect(Target, Range("B5:D10")) Is Nothing Then
      Target.Value = Target.Value + 1
      Cancel = True
   End If
End Sub

 

[tuggers1975]

Many thanks for that.............
what if it was multiple ranges such as B5:H20 and K5:Q20 etc, etc

 

[Fluff]

You can do that like

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Not Intersect(Target, Range("B5:H20,K5:Q20")) Is Nothing Then
      Target.Value = Target.Value + 1
      Cancel = True
   End If
End Sub

 

[tuggers1975]

brilliant thank you

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[tuggers1975]

ok.........
I have used the code advised so kindly and can confirm that it works perfectly!!
However...……….
I have been asked to make some adjustments to the sheet!!
I have been asked to introduce a new column that will initially affect the range used but I am capable of adjusting that
My query is two fold......
Firstly how do I adapt the original code to include additional multiple ranges, but where the double click will result in a timestamp instead of incremental numbers.
secondly, is there any way that these cells can be protected so that the only entry allowed is the double click time stamp, removing the ability for the data to be overwritten or fabricated.

I would greatly appreciate you help with this...…….below is my current source code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("B5:H20,K5:Q20,B30:H45,K30:Q45,B55:H70,K55:Q70,B81:H96,B23,K23,B48,K48,B73,K73,B99")) Is Nothing Then
Target.Value = Target.Value + 1
Cancel = True
End If
End Sub

 

[Peter_SSs]

See if this is sufficient.

1. Select the whole worksheet by clicking this box:
   
   
   
   
2. Format Cells.. and ensure this box is unchecked:
   
   -> OK
   
   
3. Select allyour target cells and
   • Format with the Date &/or Time format that you want and ensure the columns are wide enough to display the result.
   • Format Cells.. and ensure the 'Locked' box shown above is checked -> OK
     
4. Use the code below after modifying it as indicated by the comments.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Not Intersect(Target, Range("E1:E5,H6,K9:M14")) Is Nothing Then '<- Change to your own ranges
      Cancel = True
      With ActiveSheet
        .Unprotect Password:="abc" '<- Choose your own password
        Target.Value = Now
        .Protect Password:="abc"  '<- Use same password as above
        .EnableSelection = xlNoRestrictions
      End With
   End If
End Sub