Click one button to activate other buttons in other sheet RRS feed

  • Question

  • Hi,

    I have a Excel file with three sheets. On sheet 2 and sheet 3 are buttons (for the solver on that sheet). Now I want to have a button on sheet 1 that activates the buttons on sheet 2 and sheet 3 (so that the solver process will start for both sheets).

    How can I do this?

    Thanks in advance.


    Saturday, October 7, 2017 9:15 PM

All replies

  • What type of button controls? ActiveX or Forms Controls?

    If forms control then in the code that is called by the button you can place a call to the code for the other buttons. However, it will only process one at a time until each called sub is finished and returns to the calling code.

    Also, if using Forms Controls then the same module can be called from multiple buttons.

    I don't believe it will work with ActiveX controls even if the module name is included in the call.

    Regards, OssieMac

    Sunday, October 8, 2017 10:26 AM
  • That would be something like this:

          Sheets("Sheet 2").Select
          Application.Run "The macro for button on sheet 2"

          Sheets("Sheet 3").Select
          Application.Run "The macro for button on sheet 3"

    Notice, that if the macros are located in the sheets object, you might have to prefix the macro name with the sheet object name:

          Application.Run "Sheet2.The macro for button on sheet 2"

    Change the names accordingly with your own names  :-)

    Sunday, October 8, 2017 10:43 AM
  • Hello Ganesh,

    If the button is form control, please use Call statement. For more information, please visit Calling Sub and Function Procedures .

    If it is ActiveX control, please change Private declaration into Public and then use CallByName Function

    Public Sub CommandButton1_Click()
    MsgBox ("Hello2")
    End Sub
    Sub DEMO()
    'Form control Button1 assigned macro named "Button1_Click"
    Call Button1_Click
    'Active conrtol named "CommandButton1"
    CallByName Worksheets("Sheet2"), "CommandButton1_Click", VbMethod
    End Sub



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, October 12, 2017 7:45 AM