CODEWITHSUNDEEP
excelcontrolsuserformvba
kan
kan

Reputation: 3

write One code for two controls? For example Two CommandButton

In my userform, there are CommandButton1, 2, 3, 4, 5, 6........,and they all do the same task。 For example: Range("A1").value = "Good"。

Here comes the question, is there any way that I can just write one code for all of them?

If the answer is yes, can I make range variable? For example: CommandButton1: range("A1").value = "Good", CommandButton2: range("A2").value = "Good"。

Many thanks for your reply.

Upvotes: 0

Views: 51

Answers (1)

Andy G
Andy G

Reputation: 19367

VBA does not have a sophisticated event model. Instead, you can create, and call, a common sub procedure, passing the particular argument(s) that the sub needs to determine its outcome:

Private Sub CommandButton1_Click()
    Call Clicker("A1")
End Sub

Private Sub CommandButton2_Click()
    Call Clicker("A2")
End Sub

Private Sub Clicker(sRange As String)
    'MsgBox sRange
    Range(sRange).Value = "Good"
End Sub

The parameter does not have to be a string, it could be a Range.

Or the value "A1", etc., could be stored, and retrieved, as a property of the form (rather than passing it as an argument).

A possible alternative is to check the ActiveControl and then behave accordingly:

Private Sub Clicker()
    MsgBox ActiveControl.Name
    'do something according to the name
End Sub

I don't like this and prefer the first alternative, as the value(s) are likely to be specific to the clicked button anyway (and the button name may change). The code may also be called without a relevant button being active.

A third alternative is to create your own custom classes and event model, which would require some research.

Here is an example using a custom property of the UserForm:

Private sCellOfInterest As String

Private Property Get CellOfInterest() As String
    CellOfInterest = sCellOfInterest
End Property

Private Property Let CellOfInterest(ByVal sRange As String)
    sCellOfInterest = sRange
End Property


Private Sub CommandButton1_Click()
    CellOfInterest = "A1"
    Call Clicker2
End Sub

Private Sub CommandButton2_Click()
    CellOfInterest = "A2"
    Call Clicker2
End Sub


Private Sub Clicker2()
    MsgBox CellOfInterest
End Sub

Again, the property could be an object rather than a string, then Property Set would be used rather than Property Let.

Upvotes: 1

Related Questions