CODEWITHSUNDEEP
excelvbabutton
Frosty
Frosty

Reputation: 13

changing values in cells across multiple sheets with macro

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 :)

Upvotes: 1

Views: 15803

Answers (2)

peege
peege

Reputation: 2477

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

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96771

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

Upvotes: 0

Related Questions