one sub for many Combos in different way
 

Hi,
I have following problem. I have variable count of sheets (+-8) and on
each of them 31ComboBoxex. I want one procedure for all combos in my
workbook. I am doing it in this way:

I have ClassModule with name ComboClass:

Public WithEvents ComboGroup As ComboBox

Private Sub ComboGroup_Change() 'this is the sub I want to run whe
someone click on any Combo
MsgBox "working!" 'just easy example
End Sub



Dim Comba() As New ComboClass 'global variable

And this is the sub that is filling ComboGroup:

Private Sub Filling()
Dim MemberCount As Integer
Dim Members As OLEObjects
Dim Member As OLEObject
Dim Lists As Sheets
Dim List As Worksheet

Set Lists= ThisWorkbook.Sheets
MemberCount = 0

For Each List In Lists
For Each Member In List.OLEObjects
If TypeName(Member.Object) = "ComboBox" Then
Member.ListFillRange = "List1!D6:D9" 'not important
Member.Object.ListIndex = 0
MemberCount = MemberCount + 1
ReDim Preserve Comba(1 To MemberCount)
Set Comba(MemberCount).ComboGroup = Member.Object
End If
Next Member
Next List
End Sub


It looks easy and it is. Problem is that I have almost 300 Combos and
the procedure Filling takes about 0.2sec and it is too much. And other
(and most important) disadvantage is that I must every Workbook_open
an when someone adds list with combos reinicilize ComboGroup (with sub
Filling).

Is there a possibility to make all combos processing same procedure
without "filling"? How to make myCombo that will do it in default way.
Sorry for my poor english :-(
and THX
joch.