macro to increase a cell’s value based on a reference

**wiggles8831** · 06-28-2011, 03:26 PM

I am making an Excel-based set of flash cards (attached). I need to make a macro to increase a cell’s value by 1 based on a reference, and a second macro to decrease it by 1.

The first sheet (List) has a list of words in German (column B) and the English translation (column C). Column A assigns a number value to each word, which is referenced by the sheet “Flashcards”. Column D is the “Score” for each word.

On the second sheet (Flashcards) cell C7 takes a random number from List.A:A which cell E7 uses to pull that number’s word in English or German.

On that same sheet, I have two command buttons. I would like a macro to add 1 to that word’s score (first sheet, column D) if you click “Correct”. The second macro would deduct 1 upon clicking “Incorrect”

(Note: this is a growing list so it needs to be able reference the entire column)

I’m a noobie to both VBA and this site so any help is much appreciated!

**tigeravatar** · 06-28-2011, 03:37 PM

wiggles8831,

Attached is a modified version of your workbook. Both buttons have been assigned to the following macro:

Sub UpdateScore()
    
    
    Dim rngDest As Range: Set rngDest = Sheets("List").Range("D" & Sheets("Flashcards").[C7].Value + 1)
    If ActiveSheet.Buttons(Application.Caller).Characters.Text = "Correct" Then
        rngDest.Value = rngDest.Value + 1
    Else
        rngDest.Value = rngDest.Value - 1
    End If
    
    While Sheets("Flashcards").[C7].Value = rngDest.Row - 1
        Application.Calculate
    Wend
    
End Sub

Hope that helps,
~tigeravatar