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.

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.

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.

Felipe,

Here's a different idea based on your last question. It doesn't use
buttons, so I don't know if it will work for you.
In the worksheet name a range that has all the cells your working with - I
named it "click_to_change." Then in the worksheet module paste these two
subs:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Not Intersect(Target, Range("click_to_change")) Is Nothing Then
Target.Value = Target.Value + 1
Cancel = True
End If
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)
If Not Intersect(Target, Range("click_to_change")) Is Nothing Then
Target.Value = Target.Value - 1
Cancel = True
End If
End Sub

Now double-clicking will add and right-clicking will subtract. Note that
the normal results of double- and right-clicking are cancelled.

hth,

Doug

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

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.

add this to the SHEET module and it will increase the cell you double click.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
On Error GoTo fixit
Application.EnableEvents = False
If Target.Value = 0 Then oldvalue = 0
Target.Value = 1 + oldvalue
oldvalue = Target.Value
fixit:
Application.EnableEvents = True
End Sub

--
Don Guillett
SalesAid Software

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

add this as the line just before end sub

Cancel = True

--
Don Guillett
SalesAid Software

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