New to VBA

[danirock]

I'm quite new with VBA in excel, and recently saw someone create a spreadsheet where they made achievements for a game called Morrowind (all credit goes to Morrowind – Now with Achievements! | Visit Scenic Magical Elf Land for creating the original spreadsheet).

I thought I could use that spreadsheet and come up with a way of using a checkbox to both mark off (i.e. change the background color in the cell to state that I got that achievement as well as add the amount of the "gamerscore" of the particular achievement to the total amount).

Heres a snippit of my worksheet:

Morrowind 0/1000
e.g. Start the game 5

Essentially, the first row is the game title and the total amount of gamerscore. I want to know how to use VBA (or if there is a better way of accomplishing my task) to have the 1000 as a constant and only change the "0" in "0/1000" by whatever the score of an achievement is (i.e. 5 for Start the Game) when I check the box.

I have no issue with changing the background color of the cell when the box is checked, but I can't seem to have the values in cell B1 (the cell with "0/1000") by the value of any gamerscore.

Heres a snippit of my code:

Sub AddGamerscore()
Dim A As Integer
A = 0
Const B As Integer = 1000
Range("B1").Value = A & "/" & B
End Sub

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then Range("A2").Interior.ColorIndex = 3
If CheckBox1.Value = True Then A = Range("B2") + A
If CheckBox1.Value = False Then Range("A2").Interior.ColorIndex = 0
If CheckBox1.Value = False Then A = A
End Sub

Nothing displays in cell B1 except for the "/". I assume I'm doing something wrong, but with very limited knowledge of VBA, I can't figure out what I'm doing incorrectly. Also, if there is a better way of accomplishing this, please let me know.

 

[AlphaFrog]

You may be able to do it with just a formula in B1

=SUM(B2:B50)&"/1000"

Where B2:B50 is the sum of the "achievements"


Then just use the checkboxes to color and insert achievement points

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CheckBox1_Click()
    [COLOR=darkblue]If[/COLOR] CheckBox1.Value = [COLOR=darkblue]True[/COLOR] [COLOR=darkblue]Then[/COLOR]
        Range("A2:B2").Interior.ColorIndex = 3
        Range("B2").Value = 5
    [COLOR=darkblue]Else[/COLOR]
        Range("A2:B2").Interior.ColorIndex = xlNone
        Range("B2").Value = 0
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]