Repeating procedures using values from different comboboxes

[djburnheim]

I have 10 comboboxes on a form, the procedure that runs for each combobox is exactly the same. Rather than re-typing the code for each combobox is there a way to make it repeat for each...I've looked at for/next, for each and loop statements but can not work out a way to do it.

If combobox1.text <> &quot;&quot; then
'Do something
End If

If combobox2.text <> &quot;&quot; then
'Do the samething
End If

 

[Stretchwickster]

Try this:

Dim ctrl As Control
    For Each ctrl In UserForm1.Controls
        If TypeName(ctrl) = "ComboBox" Then
            'Do something here
        End If
    Next ctrl

Hope this helps Clive

 

[djburnheim]

Clive thanks for the help, I've run into a problem though...I have 2 comboboxes for each day of the week (Mon - Fri) from the values of the comboboxes I find a cell in that day's column and fill it with a colour then move on to the next day and repeat. I've stepped through the code and it doesn't seem like the For each ctrl statement checks the controls in any particular order. I've tried breaking the controls up into different frames and realize I could break them up into 5 groups (one for each day) but is there a better way?

Thanks for the help
Dave

This is the code I have at the moment....

'Inbox Duties
dColumn = 4

'Check all controls in early frame    
For Each ctrl In frmWeeklyStats.fraInbox.fraEarly.Controls
        If TypeName(ctrl) = "ComboBox" Then
            If ctrl <> &quot;&quot; Then
                With Sheets(&quot;Weekly Stats&quot;)
                    Set fCell = .Columns(&quot;C&quot;).Find(ctrl, LookIn:=xlValues, lookat:=xlWhole) 'find row with matching analyst
                        If Not fCell Is Nothing Then
                            aRow = fCell.Row
                        End If
                End With
         
                'Select Cell using row with analyst's name & column for day of the week & fill
                If aRow <> 0 Then
                    Cells(aRow, dColumn).Select
                    Cells(aRow, dColumn).Activate
                        With Selection.Interior
                            .ColorIndex = 4
                            .Pattern = xlSolid
                        End With
                End If
            End If
            dColumn = dColumn + 2 'next day
        End If
Next ctrl

dColumn = 4
    
'Check all controls in early frame
For Each ctrl In frmWeeklyStats.fraInbox.fraLate.Controls
        If TypeName(ctrl) = &quot;ComboBox&quot; Then
            If ctrl <> &quot;&quot; Then
                With Sheets(&quot;Weekly Stats&quot;)
                    Set fCell = .Columns(&quot;C&quot;).Find(ctrl, LookIn:=xlValues, lookat:=xlWhole) 'find row with matching analyst
                        If Not fCell Is Nothing Then
                            aRow = fCell.Row
                        End If
                End With
         
                'Select Cell using row with analyst's name & column for day of the week & fill
                [COLOR=blue]If aRow <> 0 Then
                    Cells(aRow, dColumn).Select
                    Cells(aRow, dColumn).Activate
                        With Selection.Interior
                            .ColorIndex = 4
                            .Pattern = xlSolid
                        End With
                End If
            End If
            dColumn = dColumn + 2 'next day
        End If
Next ctrl

 

[Stretchwickster]

I don't have time to look at your code properly - maybe others with more time could?!

The following code will work through each control in order displaying the name of the current combobox:

For counter = 0 To UserForm1.Controls.Count - 1
  If TypeName(UserForm1.Controls(counter)) = "ComboBox" Then
    MsgBox UserForm1.Controls(counter).Name
  End If
Next counter

Clive

 

[combo]

May be you can organize your controls using array, for instance:

[tt]Dim MyBoxes(5,2) as Controls[/tt]

assign objects (10 settings):

[tt]Set MyBoxes(1,1)=UserForm1.ComboBox1
Set ................................[/tt]

and refer to them via MyBoxes(x,y) ?

 

[djburnheim]

Can I change the order in which controls are checked? I did what Clive suggested to see what controls where being checked(which I was also able to do by stepping through the code using F8 and checking the value of ctrl at each loop). It isn't going by tab order or any other order I can see.

In terms of organising them in an array, I'm not really sure where to go after setting them all...

Dim DayBoxes(5, 2) As Control

Set DayBoxes(1, 1) = frmWeeklyStats.cboMonday7
Set DayBoxes(2, 1) = frmWeeklyStats.cboTuesday7
Set DayBoxes(3, 1) = frmWeeklyStats.cboWednesday7
Set DayBoxes(4, 1) = frmWeeklyStats.cboThursday7
Set DayBoxes(5, 1) = frmWeeklyStats.cboFriday7

Set DayBoxes(1, 2) = frmWeeklyStats.cboMonday11
Set DayBoxes(2, 2) = frmWeeklyStats.cboTuesday11
Set DayBoxes(3, 2) = frmWeeklyStats.cboWednesday11
Set DayBoxes(4, 2) = frmWeeklyStats.cboThursday11
Set DayBoxes(5, 2) = frmWeeklyStats.cboFriday11

 

[Stretchwickster]

In regard to the order: I found that it looped through in the order I created them. Clive