I would suggest the following instead to allow you to keep track of goals and behindsI haven't done macros in like 5 years so i thought i would give it a try, managed to get a working scoreboard using the above colours as a ref
View attachment 1071316
Currently it's pretty minimal but it does the following:
- 1 click button to add Goal or behind, same to remove (in case of score decision change), no need to manually calculate
- Team selection by a list, auto-populating the nickname on scorebug
- Conditional Logos on team selection they auto-populate using Power Of Optimism's amazing minimal logos
Things i could change
- Add conditional colours on team background
- Stopwatch function (start/stop timer)
- Conditional data grab based on quarter that grabs stop clock time, stats and feeds into a second scorebug with overall view
- Button to change the "QTR" state
- Potential flashing text or animation on Goal Add
- Button for temporarily (fade id/fade out) score worm (excel graph), assigning a timestamp to each button click so that goals for and against can be tracked on a table
To be honest macros are nifty, i'd forgotten how powerful excel can be
if anyone is keen, the main macro for adding a value to a cell is
Sub T1GoalAdd()
Sheet1.[E24] = Sheet1.[E24].Value + 6
End Sub
Where T1GoalAdd is the macro name, in this case assigned to the first button for Team 1
E24 is the current score for Team 1 and the Value is the score value to add
You can manipulate this macro for behinds etc
C-like:
Sub T1GoalAdd()
Sheet1.[*goals cell*] += 1 // += is the same as saying variable = variable + x
CalcScores()
End Sub
Sub CalcScores()
Sheet1.[*home total score*] = (Sheet1.[*home goals*] * 6) + Sheet1.[*home behinds*]
Sheet1.[*away total score*] = (Sheet1.[*away goals*] * 6) + Sheel1.[*away behinds*]
End Sub
Doing it that way would allow you to more easily change score values AND force the sheet to recalculate every time the score changes. I don't know much VBA but this looks like pretty standard Visual Basic code which I am familiar with. You could do a nested function where each button passes the team, type of score and whether it is adding or subtracting, such as this:
C-like:
Sub ScoreChange(Home, Goal, Add)
If Home
If Goal
If Add
Sheet1.[*home goals*] += 1
Else
Sheet1.[*home goals*] -= 1
Else
If Add
Sheet1.[*home behinds*] += 1
Else
Sheet1.[*home behinds*] -= 1
End If
Else
If Goal
If Add
Sheet1.[*away goals*] += 1
Else
Sheet1.[*away goals*] -= 1
Else
If Add
Sheet1.[*away behinds*] += 1
Else
Sheet1.[*away behinds*] -= 1
End If
End If
CalcScores()
End Sub
Sub CalcScores()
Sheet1.[*home total score*] = (Sheet1.[*home goals*] * 6) + Sheet1.[*home behinds*]
Sheet1.[*away total score*] = (Sheet1.[*away goals*] * 6) + Sheel1.[*away behinds*]
End Sub
Code:
// home goal add
ScoreChange(True, True, True)
//home goal subtract
ScoreChange(True, True, False)
//home behind add
ScoreChange(True, False, True)
//home behind subtract
ScoreChange(True, False, False)
//away goal add
ScoreChange(False, True, True)
//away goal subtract
ScoreChange(False, True, False)
//away behind add
ScoreChange(False, False, True)
//away behind subtract
ScoreChange(False, False, False)
Let me know if any of that helps - it can keep your program smaller than having one main function for similar tasks than having 8 separate functions. It also makes debugging easier in some ways because you only have one function to worry about or modify instead of 8.
EDIT: BigFooty doesn't preserve spaces or tabbing in spoilers so sorry about that.
Last edited: