By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,404 Members | 2,512 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,404 IT Pros & Developers. It's quick & easy.

Clicking buttons from other sheets (Excel)

TheServant
Expert 100+
P: 1,168
Hi guys,
Almast a virgin VB coder (~7days) but I have a question on how functions/subs are referenced. I have a button on each of my worksheets which does the same to all of them, but obviously with the different data supplied. I also have a "Total" sheet which sums up the results.

When I update a global variable in the Total sheet I want it to re-do all the calcs on each sheet. I am sure there is a way for a global function or something, but for now: How do I click/activate a button function from another sheet? This is what I ahev so far and only the first line works...

Expand|Select|Wrap|Line Numbers
  1. Private Sub total_Click()
  2.     Worksheets("Sheet1").Activate
  3.     Worksheets("Sheet1").button1()
  4. End Sub
Hope it makes sense, thanks for your help.
Jul 10 '08 #1
Share this Question
Share on Google+
4 Replies


Expert 100+
P: 634
Hi

Probably the easiest way is to have all your code in a separate code module (not the sheet object module) something like this
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Sub TotalSheetButtonSub()
  4.     Sheets("Sheet1").Activate
  5.     Sheet1ButtonSub
  6.     Sheets("Sheet2").Activate
  7.     Sheet2ButtonSub
  8.     Sheets("Total Sheet").Activate
  9. End Sub
  10. Sub Sheet1ButtonSub()
  11.     MsgBox "Sheet1 Procedure"
  12. End Sub
  13. Sub Sheet2ButtonSub()
  14.     MsgBox "Sheet2 Procedure"
  15. End Sub
You do not say which type of button you are using (Forms or Controls Toolbox Toolbar) but the code in a separate code module is avaiable to both types.


I assume you need to active each sheet because the code in each sub refers to the active sheet? If so, then this is not necessary if your code referes to a reference of the sheet in question. This is paticulary useful if the code is the same for each sheet processed as you can pass the sheet reference as an argument to the Sub and only have one instance of the code to mantain instead of multile copies of the same code.

For instance
Expand|Select|Wrap|Line Numbers
  1. Sub TotalSheetButtonSub()
  2.     SheetButtonSub Sheets("Sheet1")
  3.     SheetButtonSub Sheets("Sheet2")
  4. End Sub
  5. Sub SheetButtonSub(ByRef Sht As Worksheet)
  6.     With Sht
  7.         MsgBox "This sheet Name is " & .Name
  8.     End With
  9. End Sub
Just some thoughts and ideas, perhaps for the (not too distant!) future?

MTB
Jul 11 '08 #2

TheServant
Expert 100+
P: 1,168
Hi

Probably the easiest way is to have all your code in a separate code module (not the sheet object module) something like this
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Sub TotalSheetButtonSub()
  4.     Sheets("Sheet1").Activate
  5.     Sheet1ButtonSub
  6.     Sheets("Sheet2").Activate
  7.     Sheet2ButtonSub
  8.     Sheets("Total Sheet").Activate
  9. End Sub
  10. Sub Sheet1ButtonSub()
  11.     MsgBox "Sheet1 Procedure"
  12. End Sub
  13. Sub Sheet2ButtonSub()
  14.     MsgBox "Sheet2 Procedure"
  15. End Sub
You do not say which type of button you are using (Forms or Controls Toolbox Toolbar) but the code in a separate code module is avaiable to both types.


I assume you need to active each sheet because the code in each sub refers to the active sheet? If so, then this is not necessary if your code referes to a reference of the sheet in question. This is paticulary useful if the code is the same for each sheet processed as you can pass the sheet reference as an argument to the Sub and only have one instance of the code to mantain instead of multile copies of the same code.

For instance
Expand|Select|Wrap|Line Numbers
  1. Sub TotalSheetButtonSub()
  2.     SheetButtonSub Sheets("Sheet1")
  3.     SheetButtonSub Sheets("Sheet2")
  4. End Sub
  5. Sub SheetButtonSub(ByRef Sht As Worksheet)
  6.     With Sht
  7.         MsgBox "This sheet Name is " & .Name
  8.     End With
  9. End Sub
Just some thoughts and ideas, perhaps for the (not too distant!) future?

MTB

Fanstastic, thanks a lot for your reply. Only one thing: How do I tie a module to a control box button? But the module works exactly how I need it too so already 90% there!
Jul 14 '08 #3

Expert 100+
P: 634
Fanstastic, thanks a lot for your reply. Only one thing: How do I tie a module to a control box button? But the module works exactly how I need it too so already 90% there!
Hi

If you mean a 'Control Toolbox' button, then, based on my previous example, all you do in the 'CommandButton_Click' event is put

'TotalSheetButtonSub' to run all procedures or

'Sheet1ButtonSub' to run the specific sheet procedure.


??

MTB
Jul 14 '08 #4

TheServant
Expert 100+
P: 1,168
Hi

If you mean a 'Control Toolbox' button, then, based on my previous example, all you do in the 'CommandButton_Click' event is put

'TotalSheetButtonSub' to run all procedures or

'Sheet1ButtonSub' to run the specific sheet procedure.


??

MTB
Yup, that was it, thanks for al your help Mike, problem solved and lesson learnt!
Jul 16 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.