Which userform command button was clicked (VBA)

[haseft]

hi,
how do I find wich command button was clicked?
help with this please.

Dim i As Long
n = 11
For i = 1 To 6
  If UserForm3("cmb" & i) was clicked then ?????
    'do something
  Else
    'do something
  End If
  n = n + 1
Next

 

[haseft]

As I mentioned to RoryA, my knowledge is very very low in vba,
would you please send an simple example.
I don,t have any other codes. Its only a simple userfrom with 6 command button, nothing else.

 

[mikerickson]

An example of what.

Typically, your userform with six buttons would have six subs

Private Sub CommandButton1_Click()
    ' ...
End Sub

Private Sub CommandButton2_Click()
    ' ...
End Sub

Private Sub CommandButton3_Click()
    ' ...
End Sub

' etc.

Each of which would respond to its button being clicked.
There is no generic "some button has been clicked" event. The only way that VBA would know that the user clicked a button, would be because one of the event codes (like above) was triggered, each of which is specific to a particular button.
Since clicking each button is matched with only one event code, the question "which has been clicked" is easily answered.

In CommandButton1_Click, command button 1 has been clicked, in CommandButton2_Click, command button 2 was clicked.

If your various Click event codes call other subs and those other subs need to know which was clicked, there are different ways to pass that information to the subs. How to do that well depends on what those subs might be and how each of the Click events call them. Hence our desire to see your full code.

 

[mikerickson]

If all you want is a message box, the simplest code would be

Private Sub CommandButton1_Click()
   MsgBox "Button 1 was clicked"
End Sub

Private Sub CommandButton2_Click()
    MsgBox "you clicked #2"
End Sub

Private Sub CommandButton3_Click()
    MsgBox "three"
End Sub

' etc.

 

[haseft]

Yes I know the solution you suggest.
But I am looking if there is an other way to solve this.

 

[haseft]

Thanks RoryA and mikericsson for the comments.
I use mikericsson suggestion, sub for every button.

 

[jkpieterse]

Open the VBA editor, double-click on your userform. Then double-click on one of your buttons. The code pane belonging to the userform opens with a btn1_Click() procedure added for you. Write your code there. Repeat for the other buttons.

 

[mikerickson]

One other approach to your user interface might be to replace the six command buttons with a ListBox and one Command Button.
The user selects which of the six actions from the list and then presses a GO command button.

A similar alternative might be a TabStrip control with six tabs (buttons).

The user interface would be different (the TabStrip idea is particularly visually ugly IMO). But they are other ways to give the user six options of things to do.

 

[haseft]

jkpieterse- i am looking for a solution with a few codes. I tried this earlier. Thanks.
mikericson- very intresting way to a new solution, i vill try it tomorw. Thanks.

 

[DanteAmor]

Yes I know the solution you suggest.
But I am looking if there is an other way to solve this.

This could be another way, creating a class for buttons.

Put the following code inside your userform:

Dim colButton As Collection 'Collection of Button      'This to the top of all the code.

Private Sub UserForm_Initialize()
  Dim ctrl As MSForms.Control
  Dim clsObject As Class1
  
   'Create New Collection To Store Custom
  Set colButton = New Collection
  For Each ctrl In Me.Controls
    If TypeName(ctrl) = "CommandButton" Then
      Set clsObject = New Class1
      Set clsObject.Button = ctrl
      colButton.Add clsObject
    End If
  Next
End Sub

___________________________________________________________
Create a Class Module

In the Visual Basic For Applications (Alt+F11), choose “Insert” then “Class Module”

Then Class1 is created.
In the panel of the class1 paste of following code:

Public WithEvents Button As MSForms.CommandButton

Private Sub Button_Click()
  MsgBox "you clicked button : " & Button.Caption
End Sub

___________________________________________________________________

Run your userform, click on any button, a msgbox appears.

 

[haseft]

hi DanteAmor,
I tried this, but not working, get massage "not defifned" on line Dim clsObject As Class1
i create Class1 and put the 4 lines of codes there.
the name of CommandButton on my userform is cmb1, cmb2 and cmb3.

Dim colButton As Collection 'Collection of Button      'This to the top of all the code.

Private Sub UserForm_Initialize()
  Dim ctrl As MSForms.Control
  Dim clsObject As Class1'  get massage "not defifned" 
 
   'Create New Collection To Store Custom
  Set colButton = New Collection
  For Each ctrl In Me.Controls
    If TypeName(ctrl) = "cmb" Then
      Set clsObject = New Class1
      Set clsObject.Button = ctrl
      colButton.Add clsObject
    End If
  Next
End Sub