Handle many ComboBox_Change Event

Question

Well I'm also new in VBA programming. I'm creating a form which helps me to do quotations, and then there is a part of my form that shows items I've already registered, like this:

My Form with ComboBoxes

So the purpose of those ComboBoxes is to change or delete the correponding item according with the option I choose, and I would have a lot of them in my UserForm, making it hard to create many ComboBox event programs (like ComboBox1_Change, ComboBox2_Change, ... ComboBox50_Change). And then, the main question is: how could I do it in VBA without loosing a lot of time making the same code for different objects? I would like to create just one code for all ComboBoxes.

I understand that I can do in this way below, but I'm sure that it has a better way to do.

Sub ComboBox1_Change()
  Call myCode
End Sub

Sub ComboBox2_Change()
  Call myCode
End Sub

Sub ComboBox50_Change()
  Call MyCode
End Sub

Sub myCode()
For i=1 to 50
   If Controls("ComboBox" & i).Value = "Change" Then
      Call MySecondCode
   End If
Next i
End Sub

I spent about 30 minutes searching about this question, but I didn't find anything good for me. I hope you guys understood my question. Thanks in advance.

Update:

Axel Richter, as I said in comments, I'm having problem in this:

Private Function isNOKTest()

If prod1.Value = "" Or _
    prod2.Value = "" Or _
    tecido.Value = "" Or _
    tamanhos.Value = "" Or _
    unitario.Value = "" Or _
    quantidade.Value = "" Then

        isNOKTest = True
End If

End Function


Private myCBsWithEvents As Collection
Private Sub UserForm_Initialize()
 Set myCBsWithEvents = New Collection
 For Each c In Me.Controls
  If Left(c.Name, 8) = "ComboBox" Then
   c.AddItem "CHANGE"
   c.AddItem "DELETE"
   Set myCBWithEvents = New clsCBWithEvents
   Set myCBWithEvents.myCB = c
   myCBsWithEvents.Add myCBWithEvents
  End If
 Next


End Sub

'
'
'
'datatext.Value = Format(Now, "dd/mm/yyyy")
'bordadoqty.Value = 1
'estampaqty.Value = 1
'Itemlab.Caption = 1
'

When any code is added to the project, the event in class module doesn't work, apparently isn't linked with "Events", but I don't know what happened.

Answer 1

This can be achieved using a class module which handles the events.

Insert a class module in your project. Name it clsCBWithEvents. In this class module have the following code:

Public WithEvents myCB As ComboBox

Private Sub myCB_Change()
 If Me.myCB.Value = "Change" Then
  MsgBox Me.myCB.Name & " has changed to ""Change"""
 ElseIf Me.myCB.Value = "Delete" Then
  MsgBox Me.myCB.Name & " has changed to ""Delete"""
 End If
End Sub

In your user form have the following code:

Private myCBsWithEvents As Collection

Private Sub UserForm_Initialize()
 Set myCBsWithEvents = New Collection
 For Each c In Me.Controls
  If TypeName(c) = "ComboBox" Then
   c.AddItem "Change"
   c.AddItem "Delete"
   Set myCBWithEvents = New clsCBWithEvents
   Set myCBWithEvents.myCB = c
   myCBsWithEvents.Add myCBWithEvents
  End If
 Next
End Sub

Now every ComboBox in this user form will use this event handling.