title: Assigning a unique code to multiple commandbuttons created with VBA
body: <p>Good day,
  First post here for me! I couldn't find any answer to my problem even if stackoverflow community has helped me many times this past week to code this project.</p>
  
  <p>Here is what I want to do :
  I work as an intern for a big company that makes private jets and I've been asked to find a simple way to find information into a specific table. This table regroups all of the aircrafts sold by the company with particular information regarding each one of them.</p>
  
  <p>As I said, I manage to do a lot thanks to this website but now I have a really particular thing to do.</p>
  
  <p>When a user knows which aircraft to look for, it opens a tab ("RESULTS") with all the information about this aircraft. This is the first search option. It works like a charm!</p>
  
  <p>Here is my excel interface : <a href="http://i.stack.imgur.com/TXcpY.jpg" rel="nofollow">http://i.stack.imgur.com/TXcpY.jpg</a></p>
  
  <p>But when a user doesn't know which aircraft to look for, he has the option to search for a model or an interior owner (or both) through the User interface ("SEARCH" tab).
  The code executes and the sheet "RESULTS2" lists all the aircrafts with a command button in the search column. I've created these buttons with OLEObjects.add and everything worked well.</p>
  
  <p>Now, I need to assign a code to each one of these objects (Commandbutton1, Commandbutton2, ... , CommandbuttonN). 
  Everytime the user clicks on a commandbutton, it executes the code of the first search (the one when the user knows the aircraft number) with the aircraft number on the same row.</p>
  
  <p>I have the code I want to execute for each button which would be placed into the "RESULTS2" tab:</p>
  
  <pre><code>Private Sub CommandButton1_Click()
  
  'Variables
  Dim avion As String
  Dim lRow2 As Long
  
  'Settings
  Set sourceBook = ActiveWorkbook
  Set sourceSheet = sourceBook.Sheets("SEARCH")
  Set sourceSheet1 = sourceBook.Sheets("TABLE")
  Set sourceSheet2 = sourceBook.Sheets("RESULTS")
  Set sourceSheet3 = sourceBook.Sheets("RESULTS2")
  
  'Set "avion" as the aircraft number on the button row ("RESULTS2" tab)
  avion = sourceSheet3.Cells(6, "B").Value
  
  'Set the aircraft number on the "SEARCH" tab (User interface) as "avion"
  sourceSheet.Cells(5, "E").Value = avion
  
  'Calls the macro to execute search
  Call sourceSheet.Searchaircraft
  
  End Sub
  </code></pre>
  
  <p>The result in this particular case would be only for the first row's button (Commandbutton1).</p>
  
  <p>Is there any way to create a Private Sub for ALL command buttons?
  Or maybe creating a for loop with :</p>
  
  <pre><code>For i=1 To LastRow
  
      Private Sub "Commandbutton" &amp; i &amp; "_click()"
      [...]
      avion = sourceSheet3.Cells(i+5, "B").Value
      [...]
      End Sub
  
  Next i
  </code></pre>
  
  <p>For your information, here is the part of the code in the "SEARCH" tab which creates the commandbuttons (it is inside a for loop to create each line) :</p>
  
  <pre><code>'Bouton
              Set rng = sourceSheet3.Range("G" &amp; n)
              Set mybutton = sourceSheet3.OLEObjects.Add(ClassType:="Forms.CommandButton.1")
              rng.Borders.LineStyle = xlContinuous
              With mybutton
                  .Object.Caption = "&lt;-&gt;"
                  .Object.Font.Size = 3
                  .Left = rng.Left + 1
                  .Top = rng.Top + 1
                  .Height = 11.75
                  .Width = 60
              End With
  </code></pre>
  
  <p>Any help from you would be really appreciated!
  This thing is driving me nut since a few hours...!</p>
  
  <p>Thank you and have a nice day,
  Alex</p>
  
  <hr>
  
  <p>EDIT :</p>
  
  <p>Nutsch's answer was the one!</p>
  
  <p>Here is the code I wrote in a module : </p>
  
  <pre><code>`Public Sub WhichButton()
  
  On Error GoTo Err_cmdNewTerm_Click
  
  'Variables
  Dim avion As String
  Dim ButtonText As String
  Dim b As Object
  Dim Ligne As Integer
  
  
  'Settings
  Set sourceBook = ActiveWorkbook
  Set sourceSheet = sourceBook.Sheets("SEARCH")
  Set sourceSheet1 = sourceBook.Sheets("TABLE")
  Set sourceSheet2 = sourceBook.Sheets("RESULTS")
  Set sourceSheet3 = sourceBook.Sheets("RESULTS2")
  
  
  'Vérification de quel bouton a été appuyé
  Set b = sourceSheet3.Buttons(Application.Caller)
      With b.TopLeftCell
          Ligne = .Row
      End With
  
  
  ' Récupération du numéro de l'avion
  avion = sourceSheet3.Cells(Ligne, "B").Text
  
  ' Inscription du numéro dans la page SEARCH
  sourceSheet.Cells(5, "E").Value = avion
  
  'Calls the macro to execute search
  Call sourceSheet.Searchaircraft
  
  
  ' Fenêtre en cas d'erreur de programmation
  GoTo Exit_cmdNewTerm_Click
  
  Err_cmdNewTerm_Click:
  MsgBox Err.Description
  Resume Exit_cmdNewTerm_Click
  
  Exit_cmdNewTerm_Click:
  
  End Sub
  </code></pre>
  
  <p>And here is the code in my sheet to add buttons : </p>
  
  <pre><code>'Bouton
  Set rng = sourceSheet3.Range("G" &amp; n)
  sourceSheet3.Buttons.Add(rng.Left + 1, rng.Top + 1, 60, 11.75).OnAction = "WhichButton"
  sourceSheet3.Buttons.Caption = "Search"
  </code></pre>
  
  <p>Hope this will help someone one day.
  Thanks!
  Alex</p>