Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
table of # of rows with unique combos of values in 4 separated col | Excel Worksheet Functions | |||
Comparing two combos/cells to return a value | Excel Worksheet Functions | |||
Entering combos without converting it to dates? | Excel Discussion (Misc queries) | |||
Auto number w/ different letter-number combos in same column | Excel Worksheet Functions |