Is there a way to add a value of "1" to a cell by just clicking in the cell?
Thanks,
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.



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

--

Dave Peterson

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

"Carl" <(E-Mail Removed)> wrote in message
news:0bNDg.788$(E-Mail Removed)...
> Is there a way to add a value of "1" to a cell by just clicking in the
cell?
> Thanks,
> Carl
>
>

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

(E-Mail Removed)
(E-Mail Removed) with @tiscali.co.uk


"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>
>
>
> Carl wrote:
>>
>> Is there a way to add a value of "1" to a cell by just clicking in the
>> cell?
>> Thanks,
>> Carl
>
> --
>
> 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.

Sandy Mann wrote:
>
> 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
>
> (E-Mail Removed)
> (E-Mail Removed) with @tiscali.co.uk
>
> "Dave Peterson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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.
> >
> >
> >
> > Carl wrote:
> >>
> >> Is there a way to add a value of "1" to a cell by just clicking in the
> >> cell?
> >> Thanks,
> >> Carl
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

Thanks Dave,
Your fix works fine for me.
Carl
"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>
>
>
> Carl wrote:
>>
>> Is there a way to add a value of "1" to a cell by just clicking in the
>> cell?
>> Thanks,
>> Carl
>
> --
>
> Dave Peterson

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

(E-Mail Removed)
(E-Mail Removed) with @tiscali.co.uk


"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>