Button to add 1 to the value of a cell

[brian]

Hi everyone,
I have to compile data from a hundred word docs. It's a survey with multiple
questions. What id like to do is have a button in excel that I can click,
and it just keeps adding 1 to the desired cell so I can keep track of how
many times I see that particular answer on the word docs.

So if I have 5 columns labeled 1 through 5. every time I come across a "1"
on the WORD docs, I can hit the button in excel and it would tally in
increments of 1 into the cell.

Any ideas?

TIA

James

 

[Dave Peterson]

Inside the VBE, add this macro to a General Module:

Option Explicit
Sub Add1ToACell()
Dim myCell As Range
With ActiveSheet
Set myCell = .Range("A1")
If IsNumeric(myCell.Value) Then
myCell.Value = myCell.Value + 1
Else
Beep
MsgBox "No number in: " & myCell.Address(0, 0)
End If
End With
End Sub

Back to excel
select the sheet with the cell that gets incremented
Show the forms toolbar
(View|Toolbars in xl2003)
Insert a button from that forms toolbar onto this sheet

Assign the macro (Add1ToACell) to this button.

And test it out.

Be careful. If you click it too fast, you may increment it more than you want.

 

[Crowbar via OfficeKB.com]

Hi,

Place a command button on your spreadsheet (you will need the control toolbox
visible):
view/toolbars/control toolbar

Once you have drawn a button, double click it,
This should then load the VB Design enviroment and this text should be
present

Private Sub CommandButton1_Click()

End Sub

You need insert this syntax into the middle of this

Private Sub CommandButton1_Click()
Sheet1.Range("A1").value = Sheet1.Range("A1").value +1
End Sub

On each click a 1 will be added to the current value of cell A1

You may wish to change this cell reference

Please Note:
For this to work you must exit design mode. To do this click the top left
icon within the control toolbox.

Regards

Crowbar

 

[JE McGimpsey]

Or, a slight modification to allow for a cell initially blank:

Public Sub Add1ToACell()
Dim myCell As Range
With ActiveSheet.Range("A1")
If IsEmpty(.Value) Then
.Value = 1
Else
If IsNumeric(.Value) Then
.Value = .Value + 1
Else
Beep
MsgBox "No number in: " & .Address(False, False)
End If
End If
End With
End Sub

 

[Dave Peterson]

But if the cell is empty, then VBA will see it as 0.

 

[JE McGimpsey]

Ah, I forgot that "bug" in IsNumeric()

(well, evaluating Empty as numeric is a bug IMO).

 

[Gord Dibben]

You could use a simple Spinner Button from the Forms Toolbar.


Gord Dibben MS Excel MVP

 

[Dave Peterson]

I think it's old enough to be called a feature now <vbg>.

 

[brian]

Thanks to everyone who came to my rescue!
I used Crowbars advice and it worked smoothly!

then I read Gord's post, and now I will definitely remember the spinner
button from now on.

Thanks Again!

 

[eastmadc]

Hi everyone,
I have to compile data from a hundred word docs. It's a survey with multiple
questions. What id like to do is have a button in excel that I can click,
and it just keeps adding 1 to the desired cell so I can keep track of how
many times I see that particular answer on the word docs.

So if I have 5 columns labeled 1 through 5. every time I come across a "1"
on the WORD docs, I can hit the button in excel and it would tally in
increments of 1 into the cell.

Any ideas?

TIA

James

James,

You will need to create 5 buttons for each column you wish to
increment. Each button will execute a macro which will increment the
respective column by 1. The macro will look like this.

Sub IncResponse1()

If (ActiveWorkbook.ActiveSheet.Range("G2").Value = "") Then
ActiveWorkbook.ActiveSheet.Range("G2").Value = 1
Else
ActiveWorkbook.ActiveSheet.Range("G2").Value =
ActiveWorkbook.ActiveSheet.Range("G2").Value + 1
End If

End Sub

Where G2 is the cell in which you want to update.

It's easier to create your macros first and then create the buttons
because when you draw a button from the form toolbar it will prompt
you for the macro that you would like to run when that button is
pressed.


Regards,

Dustin

 

[ilia]

Why don't you have Excel comandeer Word and do this automatically?