Point system for children using command buttons

Jalize

New Member
Joined
Mar 10, 2023
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Hello

I want to create a spreadsheet to manage a point system for 300 children. They all start with 1000 points. They lose points for certain behaviour and they get points for others - and there is no pattern. I have a POINTS column A, a NAME column B and a DATE column C. I'd like to click on the value in the POINTS column, next to a specific name and, by clicking on a command button, increase or decrease the value in the POINT cell by various increments (+5, +10, +2 or -1, -2, -3, -5, -10). Then, I'd like that day's date to appear in the DATE column.

With so many children, it must be as easy as possible to give- or deduct points.

I know how to increase/decrease the value of a specific cell using a command button, but I can't figure out what the VBA code must say to do the same thing in any cell, at random times - and how to automate the appearance of that day's date.

1678454300155.png

Is it possible? Can someone help?
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
@Jalize Maybe try like below.

Code to be called by your button macros
VBA Code:
Sub Allocate (Points As Integer)
'check that selection is a single cell in column A and that B contains a name
'otherwise ignore
   If Selection.Cells.Count > 1 Then Exit Sub
   If Not Selection.Column = 1 Then Exit Sub
   If Not Selection.Offset(0, 1) > "" Then Exit Sub
'add / deduct points
   Selection = Selection + Points
'put date in column C
   Selection.Offset(0, 2) = Date
End Sub

Examples of typical macros to be allocated to your buttons so that the desired + or - Points values are used by the Allocate Macro

VBA Code:
Sub Minus_1()
  Call Allocate(-1)
End Sub


VBA Code:
Sub Plus_10 ()
  Call Allocate(10)
End Sub

Hope that helps.
 
Upvote 1
That makes complete sense, but I can't get the button to work... Is there not a Range missing?

My process: I went to DEVELOPER. I inserted an ActiveX COMMAND BUTTON. I named it "Add 10 points" and coloured it green. Then I double-clicked and entered your code, with Dim Points as Integer. Then I closed VBA, selected a cell in column A and tried clicking the button, but it won't click. I'm just selecting it. Nothing happens.

I am familiar with repetitive VBA code. Even with looping through all sheets in an Active Workbook and collating data on a new sheet, but the buttons are new to me and I am out of my depth.
 
Upvote 0
Actually, it adds the date correctly, but it does not increase the number of point. That stays 1000.
 
Upvote 0
@Jalize Maybe try like below.

Code to be called by your button macros
VBA Code:
Sub Allocate (Points As Integer)
'check that selection is a single cell in column A and that B contains a name
'otherwise ignore
   If Selection.Cells.Count > 1 Then Exit Sub
   If Not Selection.Column = 1 Then Exit Sub
   If Not Selection.Offset(0, 1) > "" Then Exit Sub
'add / deduct points
   Selection = Selection + Points
'put date in column C
   Selection.Offset(0, 2) = Date
End Sub

Examples of typical macros to be allocated to your buttons so that the desired + or - Points values are used by the Allocate Macro

VBA Code:
Sub Minus_1()
  Call Allocate(-1)
End Sub


VBA Code:
Sub Plus_10 ()
  Call Allocate(10)
End Sub

Hope that helps.
Got it! It works!

Sub CommandButton1_Click()

Dim Points As Integer

'check that selection is a single cell in column A and that B contains a name
'otherwise ignore
If Selection.Cells.Count > 1 Then Exit Sub
If Not Selection.Column = 1 Then Exit Sub
If Not Selection.Offset(0, 1) > "" Then Exit Sub
'add / deduct points
Selection = Selection + 10
'put date in column C
Selection.Offset(0, 2) = Date

End Sub
 
Upvote 0
Solution
Maybe only half got it?

Looks like you are writing the full 'Allocate' code for each and every button, each with a hard coded points value?

If you have my 'Allocate' code in the sheet module, it will expect to be called with a variable parameter -- 'Points'

So for example, your CommandButton 1 code would just be...

VBA Code:
Sub CommandButton1_Click()
  Call Allocate (10)
End Sub
 
Upvote 0

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top