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

user19976194 0
Viewed 35

I feel like I'm missing something fairly simple here, but I'm trying to set up a spreadsheet with a activex command button that when I click it sets the value of multiple cells.However when clicked it only ever seems to execute the first line that effects a cell value, and then ignores the rest. Each line works independently if I put it at the top, but not sure how to get them all to work?

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

If I use the above and then click the button it changes cell A5 as desired but does nothing to cell A8, or any cell in sheet 2. I'm fairly sure it's not an issue with the individual lines as if I comment out the line changing cell A5 suddenly the one changing cell A8 works.

vba excel
1 Answers

Try it:

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
Ranier Jardim0
Related