How can a button automatically increase a value by one?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
Smiley

More simply

For each c in selection
c=c+1
Next

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

Regards
Peter
 
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
 
Connect a button to this macro (which will work on any selected cell).


Code
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Circular reference toolbar not working 3
how do i increase the value of numbers in a column by a percentage 5
Increasing all values in spreadsheet by same percentage. 1
How to increase all values in my spreadsheet by 10% automatically 6
Windows 10 How to improve network speed in Windows 10 by increasing IRPStackSize 5
Increase values on formattted sheet 3
increase an entire spreadsheet by a percentage 2
increasing amounts in cells automatically 2

Back
Top