How can a button automatically increase a value by one?

D

Defoes Right Boot

Using Excel 2002. I have a simple list of numeric data and I want to have a
button for each item which will simply increase the value of the item by 1. I
can't seem to do this without getting a circular reference.
 
S

SMILE

Try

Eg: You have a number in A1 and try the following macro.

What the macro is doing adding 1 to A1 (in D1) then copy- paste valu
in D1 itself then copying the value of D1 to A1.


Sub Macro1()
Range("D1").Select
ActiveCell.FormulaR1C1 = "=RC[-3]+1"
Range("D1").Select
Selection.Copy
Range("D1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Range("D1").Select
Application.CutCopyMode = False
Selection.Copy
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Su
 
P

Peter Atherton

Smiley

More simply

For each c in selection
c=c+1
Next

or x= A1+1
range("A1") = x

Regards
Peter
 
D

Dave Peterson

How about incrementing the value by one if you double click on it. And
Decrement it if you rightclick on it.

If that's ok, rightclick on the worksheet tab that should have this behavior and
select view code. 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

On Error GoTo errHandler:

With Target
If IsNumeric(.Value) Then
Application.EnableEvents = False
.Value = .Value + 1
Else
Beep
End If
End With

errHandler:
Application.EnableEvents = True

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

On Error GoTo errHandler:

With Target
If IsNumeric(.Value) Then
Application.EnableEvents = False
.Value = .Value - 1
Else
Beep
End If
End With

errHandler:
Application.EnableEvents = True

End Sub

I limited my range to column A, but you could be more specific:

If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
could be:
If Intersect(Target, Me.Range("a13:a27")) Is Nothing Then Exit Sub
 
Top