Multiple command buttons in sequence with one another

Question

I am new to programming and I want to create an application for basketball coaches to be able to keep stats of their players by using VBA in Excel with command buttons.

Instead of manually inputting stats for each player in a game, I want to be able to click a command button with a players name on it to select that player's row. Then click a button that will have an action on it and input a number.

I will have 12 buttons for the players and 40 buttons for the actions (e.g. pts, reb,stl,etc.)

For example: When I click the player's name button, it will select the row that the player's attribute are in. Then when I select the action (e.g. points), it will add the number 2 to the column labeled points.

I want to use the action buttons for all 12 players so only put the number in when i click the player's name button. So the "pts" button will work for all 12 player buttons. Overall, I want to make a stat sheet for the coaches with command buttons instead of moving the cursor and inputting the information in manually.

Any suggestions on how to do so? Thank you in advance.

Clancy

Either make the 12 "player" buttons select a cell on the relevant row, or set a module-scoped variable that indicates which row is relevant. Then, when the other buttons are pressed you can use `Selection.Row`, or the module-scoped variable, to indicate which row needs to be updated. (I would use the variable approach myself, but that's mainly just personal preference.) — YowE3K, Sep 10 '17 at 23:24
Thank you for that information. I am really new to this, by any chance, can you give me an example of what you would do? For Example, I have Player A, Player B, Player C in column A. I have Rebounds in Column B, Points in column C, and Steals in Column D. So if Player A scores 2 points, I will click player A then click points and it will input 2 points on that row. Then player B scores, I will click player B and points and it will enter 2 points on that row. Are you able to give me an example for 1 or 2 players and 1 or 2 actions? Thank you very much — S.Clancy, Sep 10 '17 at 23:41
First thing I thought of when reading this was pictures. You can put pictures on an excel sheet and trigger code if someone clicks on it. So have it that if someone clicks on person 1 then a macro puts the number 1 in a cell somewhere. Then when you click on one of the actions buttons the code associated with that action will read the cell that has the person number in and knows where to put the data. Is it feasible to click on someone and bring up a form with all the action buttons on ? I would think this may be the neatest solution for you. — perfo, Sep 11 '17 at 00:30

score 2 · Accepted Answer · answered Sep 11 '17 at 00:16

Some example code, using a module-scoped variable to store which player is being processed, might be:

Option Explicit

Private CurrentPlayerRow As Long

Sub PlayerA_Click()
    CurrentPlayerRow = 3
End Sub
Sub PlayerB_Click()
    CurrentPlayerRow = 4
End Sub
Sub PlayerC_Click()
    CurrentPlayerRow = 5
End Sub

Sub Action1_Click()
    'Update column D by adding 2 to the cell value
    Cells(CurrentPlayerRow, "D").Value = Cells(CurrentPlayerRow, "D").Value + 2
End Sub
Sub Action2_Click()
    'Update column G by adding an inputted number to the cell value
    Cells(CurrentPlayerRow, "G").Value = Cells(CurrentPlayerRow, "G").Value + CLng(InputBox("Enter a number:"))
End Sub

(Not knowing anything about basketball scoring and/or statistics, I wasn't sure what sort of actions you wanted to process.)

Multiple command buttons in sequence with one another

1 Answers1