Macro to increment cells using buttons

[may178]

Hello everyone

I'm trying to solve a basic problem using form controls (Buttons) and macros but don't have a very good idea about how to exactly go on about it. Could you please help me out?


Problem Description:

A B and C are 3 buttons which increment the value of A, B or C by 1 respectively, for any particular round.

"Next Round" button moves between columns from Round 1 to Round 2 and so on forth up to N number of rounds

Clear resets all the values to 0 and starts all over from Round 1.

 

[may178]

 

[lrobbo314]

How about the code below. Assign the same macro to each button. The code assumes that you've named the buttons btn_A, btn_B,..., btn_Next, btn_Clear.

Public Round As Integer

Sub Main()
If Round = 0 Then Round = 2
If Cells(2, Round) = vbNullString Then Cells(2, Round) = "R" & Round - 1

Select Case Application.Caller
    Case "btn_A"
        Cells(3, Round) = Cells(3, Round) + 1
    Case "btn_B"
        Cells(4, Round) = Cells(4, Round) + 1
    Case "btn_C"
        Cells(5, Round) = Cells(5, Round) + 1
    Case "btn_D"
        Cells(6, Round) = Cells(6, Round) + 1
    Case "btn_E"
        Cells(7, Round) = Cells(7, Round) + 1
    Case "btn_Next"
        Round = Round + 1
    Case "btn_Clear"
        Range("B2:Z7").ClearContents
        Round = 2
End Select

End Sub

 

[may178]

Hello

Thanks for helping me out on this. I seem to be doing something wrong, I did rename all the buttons exactly according to ones in the Case, but there is no increment action or next round or clear..


I seem lost after trying it out, could you please guide me further?

 

[may178]

Hello

Thanks for helping me out on this. I seem to be doing something wrong, I did rename all the buttons exactly according to ones in the Case, but there is no increment action or next round or clear..


I seem lost after trying it out, could you please guide me further?

How about the code below. Assign the same macro to each button. The code assumes that you've named the buttons btn_A, btn_B,..., btn_Next, btn_Clear.

Public Round As Integer

Sub Main()
If Round = 0 Then Round = 2
If Cells(2, Round) = vbNullString Then Cells(2, Round) = "R" & Round - 1

Select Case Application.Caller
    Case "btn_A"
        Cells(3, Round) = Cells(3, Round) + 1
    Case "btn_B"
        Cells(4, Round) = Cells(4, Round) + 1
    Case "btn_C"
        Cells(5, Round) = Cells(5, Round) + 1
    Case "btn_D"
        Cells(6, Round) = Cells(6, Round) + 1
    Case "btn_E"
        Cells(7, Round) = Cells(7, Round) + 1
    Case "btn_Next"
        Round = Round + 1
    Case "btn_Clear"
        Range("B2:Z7").ClearContents
        Round = 2
End Select

End Sub

 

[lrobbo314]

The one thing that stands out right away is that I added shapes and signed macros. I didn't add actual form buttons. You can always use debug.print to see what application.caller values are too in order to make the select statement work too.