Increasing cell value by 1 without doing it manually

[Berrylicious]

I am collating survey data in excel 2003 and its getting tedious. For every answer that is "agree" I enter "1", but if another person answers the question in the same way ("agree") I have to manually change that number from "1" to "2", and if it was answered again I would have to change it to "3".

I hope that made sense!

Anyway, I made a counter using VBA code but I need the cell I click on to increase/decrease in value by 1, not have a button that is linked to another cell other the spreadsheet will be too many and time consuming to build.

Is there a way to increase the numbers in a single cell just by clicking that cell or a button placed over it? And have that new number appear on the button?


Thanks in advance

 

[syntaxed]

You could use the following code for incrementing the cells by 1 every time you double-click them:

Private Sub Worksheet_BeforeDoubleClick(ByVal _
       Target As Range, Cancel As Boolean)
   If Target.Column <> 2 Then Exit Sub 'change this to your column number
   If Not IsNumeric(Target) Then Exit Sub
   Cancel = True
   Target.Value = Target.Value + 1
End Sub

If you want to use buttons, e.g. a command button named CommandButton1, you can use this code for it:

Private Sub CommandButton1_Click()
    If Not IsNumeric(CommandButton1.Caption) Then
        CommandButton1.Caption = 1
    Else
        CommandButton1.Caption = CommandButton1.Caption + 1
    End If
End Sub

 

[Berrylicious]

Thanks!

For the first code, what do I pull up to enter that? Is that in the control toolbox as well??

 

[syntaxed]

For the first code:
1. Right-click the sheet
2. Left-click "View Code"
3. Paste the code in the window that appears
4. Change the column number 2 to the number appropriate for your sheet, e.g. if the cells in your sheet are in column D, then change it to 4.

 

[Berrylicious]

Can you write it so that I can copy paste it? So if I want to double click cell A1 to make it increase, what is the code?

 

[Berrylicious]

Ok that worked thanks!! but what if I want it to be across multiple columns??

 

[syntaxed]

For just cell A1:

Private Sub Worksheet_BeforeDoubleClick(ByVal _
       Target As Range, Cancel As Boolean)
   If Target.Address <> "$A$1" Then Exit Sub 'change this to your cell
   If Not IsNumeric(Target) Then Exit Sub
   Cancel = True
   Target.Value = Target.Value + 1
End Sub

For any cell in column A:

Private Sub Worksheet_BeforeDoubleClick(ByVal _
       Target As Range, Cancel As Boolean)
   If Target.Column <> 1 Then Exit Sub
   If Not IsNumeric(Target) Then Exit Sub
   Cancel = True
   Target.Value = Target.Value + 1
End Sub

 

[syntaxed]

For multiple columns you could remove this line:

If Target.Column <> 1 Then Exit Sub

That'll make it work when you double-click any cell that contains a number or blank in it.

 

[Berrylicious]

Thanks! That worked beautifully.