control the command button

[Ming]

Hi! All, Does anybody know how to use some VBA code in macro to control
the command button on the worksheet? That means the code is used the
same way as people click the command button. Thanks a lot!

[Norman Jones]

Hi Ming,

Two suggestions:

(1) Declare the CommandButton's click event Public and call it from a sub in
a normal module, e.g.:

In the Sheet module
---------------------
Public Sub CommandButton1_Click()
MsgBox "Hello Ming"
End Sub

In a normal module
--------------------
Sub Tester()
ThisWorkbook.Sheets("Sheet1").CommandButton1_Click

End Sub


(2) Alternatively,

In the Sheet Module
---------------------
Sub CommandButton1_Click()
call Tester2
End Sub

In a normal module
--------------------
Sub Tester2()
MsgBox "Hello Ming!"
End Sub

In both cases, clicking the button or invoking the ssub in the standard
macro are equvalent.


---
Regards,
Norman



"Ming" <[email protected]> wrote in message
news:[email protected]...
> Hi! All, Does anybody know how to use some VBA code in macro to control
> the command button on the worksheet? That means the code is used the
> same way as people click the command button. Thanks a lot!
>

[Norman Jones]

Hi Ming,

> In both cases, clicking the button or invoking the ssub in the standard
> macro are equvalent.

Should have read:

In both cases, clicking the button, or invoking the Sub in the standard
module are equivalent.

---
Regards,
Norman



"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> Hi Ming,
>
> Two suggestions:
>
> (1) Declare the CommandButton's click event Public and call it from a sub
> in a normal module, e.g.:
>
> In the Sheet module
> ---------------------
> Public Sub CommandButton1_Click()
> MsgBox "Hello Ming"
> End Sub
>
> In a normal module
> --------------------
> Sub Tester()
> ThisWorkbook.Sheets("Sheet1").CommandButton1_Click
>
> End Sub
>
>
> (2) Alternatively,
>
> In the Sheet Module
> ---------------------
> Sub CommandButton1_Click()
> call Tester2
> End Sub
>
> In a normal module
> --------------------
> Sub Tester2()
> MsgBox "Hello Ming!"
> End Sub
>
> In both cases, clicking the button or invoking the ssub in the standard
> macro are equvalent.
>
>
> ---
> Regards,
> Norman
>
>
>
> "Ming" <[email protected]> wrote in message
> news:[email protected]...
>> Hi! All, Does anybody know how to use some VBA code in macro to control
>> the command button on the worksheet? That means the code is used the
>> same way as people click the command button. Thanks a lot!
>>
>
>

[Ming]

Thanks a lot! Norman. Your suggestion is very helpful!

[Ming]

Hi! Norman, Just a short question: If I've got a CommandButton's click
event which is Private instead of Publich, can I still call it from a
sub in a normal module? Thanks again!

[Norman Jones]

Hi Ming,

No a private procedure in a worksheet module cannot be called from a
standard module.

If you want to keep the button's click event private, use the second
suggestion - which would be my first choice!

---
Regards,
Norman



"Ming" <[email protected]> wrote in message
news:[email protected]...
> Hi! Norman, Just a short question: If I've got a CommandButton's click
> event which is Private instead of Publich, can I still call it from a
> sub in a normal module? Thanks again!
>