1

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

pnuts
  • 58,317
  • 11
  • 87
  • 139
Frosty
  • 13
  • 1
  • 1
  • 3
  • 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 Dec 14 '14 at 22:12
  • 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 Dec 16 '14 at 14:32

2 Answers2

1

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
Community
  • 1
  • 1
peege
  • 2,467
  • 1
  • 10
  • 24
  • The other answer provided will also work in place of this sub ChangeVals. The point that if you have the same code being fired off from many places, you will want to use that code ONCE and just call it. If you need to change "ChangeVals" it's all in once place. – peege Dec 14 '14 at 19:23
  • Thank you for the quick response. The code although quite similar, will be different for each of the three buttons. So if I used your method PJ, wouldn't I have to create three different subs? – Frosty Dec 14 '14 at 19:52
  • Yes. If they are all different, I would go with a separate event for each. Skip calling them, and just perform the operations you want in the button event like Gary's Student posted. I was going off of your statement: "The other buttons are all exactly like this." – peege Dec 14 '14 at 22:06
  • On a separate note. The comment I just made is for THIS situation. In other situations, I would look into passing ARGUMENTS into the sub or function. A search on that will get you lots of good information. Let's say you have the SAME thing except one little difference, that would be a good time to have the sub take an incoming argument inside the () and have that be the part that changes. http://msdn.microsoft.com/en-us/library/aa263527%28v=vs.60%29.aspx – peege Dec 14 '14 at 22:09
0

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
Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • Thank you for the quick response Gary's Student. I tried your method first, as it was similar to what I was trying. When I tried to execute I received - Run-time Error '9' Subscript out of range. – Frosty Dec 14 '14 at 19:55
  • @Frosty **Change the sheet names to match your actual sheetnames.** – Gary's Student Dec 14 '14 at 21:06
  • I worked out where I went wrong with your solution. I used the sheet's name instead of the code name. I had to use the code name because I had already labelled the sheet name in a way excel doesn't like. I will change it back later on. Thank you for your help. Being a newbie, it was causing a lot of frustration. The processes I am creating are to try and simplify the workbook for someone else. Once again many thanks. – Frosty Dec 14 '14 at 21:51