How can I use a command button in excel to set the value of multiple cells in one click

Question

I'm trying to set up a spreadsheet with an activex command button that, when clicked, modifies the value of many cells. I feel like I'm missing something very basic here. However, it always seems to simply run the first line that affects a cell value when clicked, ignoring the others. I'm not sure how to get all of the lines to function, but each one works on its own if I put it at the top.

My code is as follows:

Private Scan As Integer

Private Sub CommandButton1_Click()                      
Scan = Sheet1.Cells(5, "A") + 1                         
Sheet1.Cells(5, "A") = Scan                             
Sheet1.Cells(8, "A") = Scan + 1
Sheet2.Cells(Scan + 4, "A") = Scan                      
Sheet2.Cells(Scan + 4, "A") = Sheet1.Cells(19, "B")     
End Sub

The button modifies cell A5 as desired if I use the above, but it has no effect on cell A8 or any other cell on sheet 2 if I use the above. Since if I comment out the line altering cell A5 suddenly the one changing cell A8 works, I'm quite sure it's not a problem with the individual lines.

Answer 1

Try this:

Private Scan As Integer

Private Sub CommandButton1_Click()                      
    Scan = Sheet1.Cells(5, "A").Value + 1                         
    Sheet1.Cells(5, "A").Value = Scan                             
    Sheet1.Cells(8, "A").Value = Scan + 1
    Sheet2.Cells(Scan + 4, "A").Value = Scan                      
    Sheet2.Cells(Scan + 4, "A").Value = Sheet1.Cells(19, "B")     
End Sub