Clicking buttons from other sheets (Excel)

[TheServant]

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...

Private Sub total_Click()
    Worksheets("Sheet1").Activate
    Worksheets("Sheet1").button1()
End Sub

Hope it makes sense, thanks for your help.

[MikeTheBike]

Hi

Probably the easiest way is to have all your code in a separate code module (not the sheet object module) something like this

Option Explicit
 
Sub TotalSheetButtonSub()
    Sheets("Sheet1").Activate
    Sheet1ButtonSub
    Sheets("Sheet2").Activate
    Sheet2ButtonSub
    Sheets("Total Sheet").Activate
End Sub
Sub Sheet1ButtonSub()
    MsgBox "Sheet1 Procedure"
End Sub
Sub Sheet2ButtonSub()
    MsgBox "Sheet2 Procedure"
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

Sub TotalSheetButtonSub()
    SheetButtonSub Sheets("Sheet1")
    SheetButtonSub Sheets("Sheet2")
End Sub
Sub SheetButtonSub(ByRef Sht As Worksheet)
    With Sht
        MsgBox "This sheet Name is " & .Name
    End With
End Sub

Just some thoughts and ideas, perhaps for the (not too distant!) future?

MTB

[TheServant]

Hi

Probably the easiest way is to have all your code in a separate code module (not the sheet object module) something like this

Option Explicit
 
Sub TotalSheetButtonSub()
    Sheets("Sheet1").Activate
    Sheet1ButtonSub
    Sheets("Sheet2").Activate
    Sheet2ButtonSub
    Sheets("Total Sheet").Activate
End Sub
Sub Sheet1ButtonSub()
    MsgBox "Sheet1 Procedure"
End Sub
Sub Sheet2ButtonSub()
    MsgBox "Sheet2 Procedure"
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

Sub TotalSheetButtonSub()
    SheetButtonSub Sheets("Sheet1")
    SheetButtonSub Sheets("Sheet2")
End Sub
Sub SheetButtonSub(ByRef Sht As Worksheet)
    With Sht
        MsgBox "This sheet Name is " & .Name
    End With
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!

[MikeTheBike]

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

[TheServant]

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!