changing values in cells across multiple sheets with macro

1
votes

I'm quite new to VBA, and have limited Excel knowledge compared to what I read on here. I am having trouble with my macros. I have a 4 sheet workbook. Each worksheet has basic command buttons that issue a simple macro( which is to minus 1 from one cell value, and add 1 in another). Where I am having trouble is creating the macro code for 3 of the buttons. These buttons need to issue similar commands,however I need them to adjust select cell values across all the worksheets,not just the active sheet the buttons are located on.

These 3 particular command buttons are located on "sheet 4". The cells are located across all 4 worksheets.Can I create a macro(activated by the single button click) that works across all 4 sheets?

Button 1 - needs to add 1 to the value of cell g10 on sheet 4 - but also minus 1 from the value of cell c4 & c7 on sheet 2
- and minus 1 from the value of cell c6 on sheet 3.

The other buttons are all exactly like this. I hope this explains enough. I am currently reading books on Excel and VBA,but being quite busy this is slow. Any help is greatly appreciated. Thank you in advance. Frosty :)

2
excelvbabutton
The key to take away from both answers is that you have to state which sheet the cell is on. Just identify what you are changing, and then declare the change. 'Sheet("SHEETNAME").Range("A1").Value =' – peege
Yes you're right PJ. I appreciate both answers I received. They both made me aware of a mistake I was making, also how to correctly achieve what i asked. I have tremendous respect for people who help and teach other people when it's not their job. Keep up the good work. Thank you again Frosty. – Frosty

2 Answers

1
votes

You just need an event for each button, then in a subroutine, name the worksheets specifically when you manipulate the cells contained. Avoid using select statements and just make the changes you need. I showed a few different ways to do the same thing, so that in the future, you can adapt according to your needs. Sometimes you will want to name the Range specifically. Sometimes you will want it to be created dynamically by some other code, or loop.

Have an event handler for the button click.

Private Sub Button1_Click()
    Call ChangeVals
End Sub

You will need to call that sub from each button click event. 

Private Sub Button2_Click()     'Repeat for the other two.
    Call ChangeVals
End Sub

This is the actual sub changing the values.

Private Sub ChangeVals()
'Using Range(ColRow)
    Sheets("Sheet4").Range("G10") = Sheets("Sheet4").Range("G10").Value + 1 

'Using Cells(rowNumber, ColLetter)  ----This is good if the column won't change.
    Sheets("Sheet2").Cells(4,"C") = Sheets("Sheet2").Cells(4,"C").Value - 1
'Using Cells(rowNumber, ColNumber)  ----This is my preferred way, loopable with numbers.
    Sheets("Sheet2").Cells(7,3) = Sheets("Sheet2").Cells(7,3).Value - 1
'Lastly
    Sheets("Sheet3").Range("C6") = Sheets("Sheet3").Range("C6").Value - 1

End Sub
0
votes

Consider:

Sub buttton1()
    Dim r1 As Range, r2 As Range, r3 As Range
    Set r1 = Sheets("Sheet4").Range("G10")
    Set r2 = Sheets("Sheet2").Range("C4,C7")
    Set r3 = Sheets("Sheet3").Range("C6")

    r1 = r1 + 1
    r2 = r2 - 1
    r3 = r3 - 1
End Sub