The attached should get you started. Theres' code in Module1:
Sub Button1_Click()
UserForm1.Show
End Sub
which is called when the user clicks the button on Sheet1.
Code in the Userfrom1's code-module:
Dim Combos() As New CmboClass
Private Sub UserForm_Initialize()
Dim ComboCount As Long, ctl As Control
ComboCount = 0
For Each ctl In Me.Controls
If TypeName(ctl) = "ComboBox" Then
ComboCount = ComboCount + 1
ReDim Preserve Combos(1 To ComboCount)
Set Combos(ComboCount).ComboGroup = ctl
End If
Next ctl
End Sub
Which put the comboxes you want to respond into a group (Combos). In this case it's all of them but you can be selective if you want.
Code in the CmboClass class module:
Public WithEvents ComboGroup As MsForms.ComboBox
Private Sub ComboGroup_Change()
Msg = "You changed " & ComboGroup.Name & vbCrLf & vbCrLf
Msg = Msg & "Its value: " & ComboGroup.Value & vbCrLf
Msg = Msg & "Left Position: " & ComboGroup.Left & vbCrLf
Msg = Msg & "Top Position: " & ComboGroup.Top
MsgBox Msg, vblnformation, ComboGroup.Name
End Sub
which just demonstrates it's working correctly.
What I haven't done is anything about changing the other comboboxes' lists of options; I can do it in a convoluted way, but it would be easier if I were able to expose the Combos object to the class module.
To delve further I'd like to see a workbook more closely representing your query.
(ps the file name I've used is NOT because it's the max number of rows in older versions of Excel, it's the number in the url of this thread! I think this tells you I might be a bit geeky.)