Point system for children using command buttons

[Jalize]

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.

Is it possible? Can someone help?

 

[Snakehips]

@Jalize Maybe try like below.

Code to be called by your button macros

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

Sub Minus_1()
  Call Allocate(-1)
End Sub

Sub Plus_10 ()
  Call Allocate(10)
End Sub

Hope that helps.

 

[Jalize]

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.

 

[Jalize]

Actually, it adds the date correctly, but it does not increase the number of point. That stays 1000.

 

[Jalize]

@Jalize Maybe try like below.

Code to be called by your button macros

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

Sub Minus_1()
  Call Allocate(-1)
End Sub

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

 

[Snakehips]

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...

Sub CommandButton1_Click()
  Call Allocate (10)
End Sub