Originally Posted by
gbonney51
I'm working on a very simple VBA scoreboard. It consists of a set command buttons with different plus and minus values and a clear button that change the total in a text box. With that said, I will have six teams which means creating six text boxes. As it stands now, I will have to create identical sets of plus and minus buttons for each of the six text boxes. How can I program the code to use only one set of plus and minus buttons and use a command button that will allow me to switch data between any of the six text boxes as needed? Thanks much.
Certainly, you can create a more dynamic solution by using a single set of plus and minus buttons and a mechanism to switch between different text boxes. Here's a simplified example to get you started:
Assuming you have three buttons (+, - and Clear) for each team, and six teams (TextBox1 to TextBox6):
Create a Module
Open the Visual Basic for Applications (VBA) editor in Excel.
Insert a new Module: Insert -> Module.
Write Code in the Module:
Copy code
' This code assumes you have three buttons named btnPlus, btnMinus, and btnClear.
' Also, you have six text boxes named TextBox1, TextBox2, ..., TextBox6.
Dim activeTextBox As Object
' This subroutine handles button clicks for plus, minus, and clear.
Sub ButtonClick(buttonName As String)
If Not activeTextBox Is Nothing Then
' Call the appropriate subroutine based on the button clicked.
Select Case buttonName
Case "btnPlus"
AddValueToTextBox activeTextBox, 1
Case "btnMinus"
AddValueToTextBox activeTextBox, -1
Case "btnClear"
ClearTextBox activeTextBox
End Select
Else
MsgBox "Please select a text box first.", vbExclamation
End If
End Sub
' This subroutine sets the activeTextBox variable based on the clicked text box.
Sub TextBoxClick(textBoxName As String)
Set activeTextBox = ThisWorkbook.Sheets("Sheet1").Shapes(textBoxName).OLEFormat.Object
End Sub
' This subroutine adds a value to the specified text box.
Sub AddValueToTextBox(textBox As Object, value As Integer)
textBox.Value = textBox.Value + value
End Sub
' This subroutine clears the specified text box.
Sub ClearTextBox(textBox As Object)
textBox.Value = 0
End Sub
Assign Macros to Buttons and Text Boxes:
Right-click on each button, go to Assign Macro, and assign the ButtonClick macro with the appropriate parameter (btnPlus, btnMinus, or btnClear).
Right-click on each text box, go to Assign Macro, and assign the TextBoxClick macro with the appropriate parameter (TextBox1, TextBox2, ..., TextBox6).
Now, when you click a text box, it becomes the active text box, and any subsequent button clicks will affect that text box. The ButtonClick subroutine determines which button was clicked and acts accordingly.