Button from the Forms Toolbar.

Keypress
Control, Ctrl : Not used
Shift, shft : Decrement by 1
alt : return to zero
Nothing : increment by 1

Assign this macro to all buttons needing to do this.

The declarations should be at the top of a general module:


Option Explicit
Declare Function GetKeyState Lib "user32" _
(ByVal nVirtKey As Long) As Integer

Const VK_SHIFT = &H10
Const VK_CONTROL = &H11
Const VK_MENU = &H12
Const KEYISDOWN = &HF0000000

Sub BtnClick()

Dim ikey As Integer
Dim btn As Button
Dim sName As String
Dim rng As Range

'CHECK SEE IF Ctrl or Alt key is down

ikey = 0
If (GetKeyState(VK_MENU) And KEYISDOWN) = _
KEYISDOWN Then
ikey = 1
End If

If ikey < 1 Then
If (GetKeyState(VK_SHIFT) And KEYISDOWN) = _
KEYISDOWN Then
ikey = 2
End If
End If

sName = Application.Caller
Set btn = ActiveSheet.Buttons(sName)
Set rng = btn.TopLeftCell
btn.Caption = rng.Value


Select Case ikey
Case 0 ' neither Ctrl or Shift
rng.Value = rng.Value + 1
Case 1 ' Alt depressed, reset to 0
rng.Value = 0
Case 2 ' shift pressed, subtract 1
If rng.Value 0 Then
rng.Value = rng.Value - 1
End If
End Select
btn.Caption = rng.Value

End Sub

If you hold down Ctrl, then you select the button, so I couldn't use ctrl.

To subtract 1, hold down Shift while you click

To zero hold down Alt while you click.

--
Regards,
Tom Ogilvy



"felipe" wrote in message
...
Wow, thanks for the prompt response. Is there a way to have the same
button
take out 1 point when clicked by holding ctrl or something like that? That
way I would only need one button per cell. The stat sheet I'm doing has
many
categories, so clearing everything would not work for me. Thanks again.

"Tom Ogilvy" wrote:

Create code like this

Public Sub BtnClick()
Dim btn as Button
Dim sName as String
Dim rng as Range
sName = Application.Caller
set btn = activesheet.Buttons(sName)
set rng = btn.TopLeftCell
rng.value = rng.value + 1
btn.caption = rng.value
End Sub


On the forms toolbar (make it visible), click on the button, then
rubberband
an area on the sheet where you want it.

You will be prompted to assign a macro (or right click on the button and
select assign macro). Assign the above macro.

A macro to clear scores

Public Sub ClearScores()
Dim btn as Button
Dim rng as Range
for each btn in Activesheet.Buttons
set rng = btn.TopLeftCell
rng.Value = 0
btn.Caption = rng.value
Next
End Sub

--
Regards,
Tom Ogilvy


"felipe" wrote in message
...
I'm trying to create a stat sheet to record points for a volleyball
match.
How can I create a macro as a button to add 1 to the cell each time I
click
it and have the number be represented in the button. Thanks.