How do you call a button in another sheet using another button in Excel?

Question

I have a command button in Sheet1 and I want to activate it using button (form control) in Sheet2.

I have written on Module1 (almost same code with command button in Sheet1)

Function DataUpdate()
If (validateVer() = False) Then
    Exit Function
End If

Dim AsCn, AsRs, SQL
Dim x, y, WDATE, NGQTY, FAQTY, RWQTY, RNQTY, TotalCell, TotalCellx
Dim FA_TBL As Variant   ' (y, x)
Dim RW_TBL As Variant   ' (y, x)
Dim RN_TBL As Variant   ' (y, x)
Dim NG_TBL As Variant   ' (y, x)


'Sheet => Table
Sheet1.Range("C2:GS2").Select
Selection.ClearContents
FA_TBL = Sheet1.Range("A2:GS2")
Sheet1.Range("C4:GS4").Select
Selection.ClearContents
RW_TBL = Sheet1.Range("A4:GS4")
Sheet1.Range("C5:GS5").Select
Selection.ClearContents
RN_TBL = Sheet1.Range("A5:GS5")
Sheet1.Range("A9:GS208").Select
Selection.ClearContents
Sheet1.Range("C9").Select
NG_TBL = Sheet1.Range("A8:GS208")

...

Sheet1.Range("A2:GS2") = FA_TBL
Sheet1.Range("A4:GS4") = RW_TBL
Sheet1.Range("A5:GS5") = RN_TBL
Sheet1.Range("A8:GS208") = NG_TBL

...

Sheet1.Range("A8:" & TotalCellx & "100").Select
Selection.Sort Key1:=Range(TotalCellx & "9"), Order1:=xlDescending, Header:=xlGuess _
    , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    SortMethod:=xlPinYin
Sheet1.Range("C9").Select
End Function

I have written on Module2 (code for my form control button at Sheet2):

Sub Command1_Click()
Sheet1.Range("C8:GF8").Value = Sheet1.Range("C240:GF240").Value
Sheet1.Range("B7").Value = Sheet1.Range("B240").Value
r = DataUpdate()
End Sub

However, I get the

"Run-time error '1004': Select method of Range class failed".

Is there any way I can call or link my command button at Sheet1 to my form control button at Sheet2?

A better option is to [avoid using `Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba), as you don't need to. — BigBen, Jun 01 '20 at 12:47

score 1 · Answer 1 · answered Jun 01 '20 at 13:03

1

In Sheet1

Public Sub ClickMe()
   MsgBox "You Clicked"
End Sub

In Sheet2 CommandButton Click Code

Private Sub CommandButton1_Click()
   Sheets("Sheet1").ClickMe
End Sub

Making the Routine Public makes it callable from outside the Sheet

answered Jun 01 '20 at 13:03

Tin Bum

1,397
1
8
16

How do you call a button in another sheet using another button in Excel?

1 Answers1