add 1 to a cell by clicking

[Carl]

Is there a way to add a value of "1" to a cell by just clicking in the cell?
Thanks,
Carl

 

[Dave Peterson]

Nope. There's nothing that fires when you click on a cell.

But maybe you could tie into the _beforedoubleclick and _beforerightclick
events:

If you want to try, rightclick on the worksheet tab that should have this
behavior. Select view code and paste this into the code window:

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub

Cancel = True 'stop editing in cell
If IsNumeric(Target.Value) Then
Target.Value = Target.Value + 1
End If
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub

Cancel = True 'stop pop up from showing
If IsNumeric(Target.Value) Then
Target.Value = Target.Value - 1
End If
End Sub

I used any cell in Column A. You can change that in both spots if you want.
Doublclicking will add 1. Rightclicking will subtract 1.

 

[Paul B]

Carl, how about double click? Right click sheet tab and view code, paste
this

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target
As Range, Cancel As Boolean)
With ActiveCell
If IsNumeric(.Value) Then _
..Value = .Value + 1
End With
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Sandy Mann]

Dave,

Why can't you use a SelectionChange event as in:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

If Intersect(Target, Range("K1")) Is Nothing Then Exit Sub

Application.EnableEvents = False

Target.Value = Target.Value + 1

Application.EnableEvents = True


End Sub

I assume that there is some reason for your not suggesting it.
--
Regards,

Sandy
In Perth, the ancient capital of Scotland

[email protected]
[email protected] with @tiscali.co.uk

 

[Dave Peterson]

If the cell is already selected, then clicking on it won't help. And if you
select the cell with the arrow keys, it'll increment then, too.

And as a personal choice, I think I would want to do something to make the cell
increment. I often select a range for other purposes.

You may still need the .enableevents for other reasons, but there's nothing in
your code that causes the _selectionchange event to fire again.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("K1")) Is Nothing Then Exit Sub

Target.Value = Target.Value + 1

End Sub

To stop any errors when more than one cell (like all of column K) is selected.

 

[Carl]

Thanks Dave,
Your fix works fine for me.
Carl

Nope. There's nothing that fires when you click on a cell.

But maybe you could tie into the _beforedoubleclick and _beforerightclick
events:

If you want to try, rightclick on the worksheet tab that should have this
behavior. Select view code and paste this into the code window:

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub

Cancel = True 'stop editing in cell
If IsNumeric(Target.Value) Then
Target.Value = Target.Value + 1
End If
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub

Cancel = True 'stop pop up from showing
If IsNumeric(Target.Value) Then
Target.Value = Target.Value - 1
End If
End Sub

I used any cell in Column A. You can change that in both spots if you
want.
Doublclicking will add 1. Rightclicking will subtract 1.

 

[Sandy Mann]

Thank you Dave,

I knew that there just had to be a reason for your choice but I was not
thinking laterally enough to see all the points you made

You may still need the .enableevents for other reasons, but there's
nothing in
your code that causes the _selectionchange event to fire again.

Yes you are quite right. I never use Worksheet_SelectionChange just
Worksheet_Change and I automatically disable events without thinking.

Thank you again

Sandy
In Perth, the ancient capital of Scotland

[email protected]
[email protected] with @tiscali.co.uk